Posted by Gert-Jan Strik on 08/28/05 00:45

Chris,

Hugo already explained the technical details. Here is an other aspect of
the issue.

Both a Primary Key and a Unique constraint are logical construct that
give information about your data. They are true regardless of the
database you use.

You have designed you schema based on some world view. You have
determined that a specific column (or set of columns) is unique, and
therefore is a candidate key for the table. What you typically do, is
choose one of the candidate keys to be the Primary Key of the table, and
you specify all the other candidate keys as Unique. It would be very
confusing to specify the same set of colums as both Unique and Primary
Key, since (by definition), the Primary Key means that the key is unique
(and non-null).

If you are looking at these topics from a performance perspective, then
I would suggest you do not use the modelling concepts (Constraints), but
limit yourself to the implementation concepts (i.e. Indexes). This means
that if you remove all indexes, but keep all constraints, then the
database would still work properly (although it might be slow). In your
example, if you want to experiment with extra indexes for performance
reasons, you can simply add a unique index.

Gert-Jan


"christopher.secord@gmail.com" wrote:
>
> Is there any advantage to doing this:
>
> ALTER TABLE testtable ADD
> CONSTRAINT PK_sysUser
> PRIMARY KEY NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100,
>
> CONSTRAINT IX_sysUser
> UNIQUE NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100
> GO
>
> over just having the primary key? Does having both an index and a
> primary key add anything?
>
> thanks
> chris

[Back to original message]