Re: Difference between UNIQUE constraint vs index

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/28/07 10:31, Joshua D. Drake wrote:
>>>> I would imagine that other DBMSes also enforce uniqueness by means of
>>>> indexes, because it'd be awful darn expensive to enforce the constraint
>>>> without one; but I'm only guessing here, not having looked. Can anyone
>>>> point to a real system that enforces unique constraints without an
>>>> underlying index?
>>
>> In Rdb/VMS (which does not use MVCC), PK (and it's alias UNIQUE)
>> constraints are independent of whether you have a unique index on
>> the table.
>
> PK is NOT an alias for UNIQUE. Yes it does have the same functional
> operation but it is technically incorrect to consider them the same.

In Rdb/VMS, which I was describing, the PK and UNIQUE constraints
(which are *not* the same as unique index) *are*

>> Now, 99.44% of the time you will *not* have a PK constraint, but
>> simply a unique index.
>
> Then you have designed your database incorrectly.

Or... *you* don't understand Rdb, and the circumstances in which it
is used.

In Rdb, a defining a PK has no automagic side effects (Which I
heartily approve of). The DBA is responsible for knowing the data
and determining the best (of multiple) way to ensuring that *that*
set of data is.

So, if you would already have put a unique index on that table,
there's no reason to also put a PK constraint on in (unless there
will also be an FK reference).

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF5ba+S9HxQb37XmcRApEOAJ9x6pco5kgqc2alEVGlEHRyOyC2WQCfQCp4
JaXCNqn0UgJGl91Kb4Suq54=
=tAbk
-----END PGP SIGNATURE-----