Primary key vs Unique key

Question

Primary key vs Unique key

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

I presumed the unique key meant the uniqueness of the collection of columns making up this key. When all values are null, that was a null unique key. When some of the columns in the unique key combination were null and some were not, then I did not consider that "unique key" to be null.

davoscollective SSCertifiable Points: 6405 More actions · Answer 2

Primary key creates a clustered index by default, only if there is no existing clustered index. It's often the case that you want a clustered index on something other than the primary key.

I find it is always best to be explicit rather than rely on defaults. Especially with things like NULL/NOT NULL on columns, and CLUSTERED.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Koen Verbeeck (10/28/2014)
serg-52 (10/28/2014)
"only one null " is someway misleading. Precisley it's a "only one null value per column".
If the unique index is not a filtered index 😉

And the link for that

http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx#bm1398825 😀

And there is also this discussion on the same topic from a QOTD.

http://www.sqlservercentral.com/Forums/Topic1402655-3290-1.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 4

davoscollective (10/28/2014)
Primary key creates a clustered index by default, only if there is no existing clustered index. It's often the case that you want a clustered index on something other than the primary key.
I find it is always best to be explicit rather than rely on defaults. Especially with things like NULL/NOT NULL on columns, and CLUSTERED.

I, too, prefer to be explicit in my code. It's more readable and protects you in case the defaults ever change in a later version.

Thanks for the question, Rabih. It's generating a terrific discussion.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 5

Mr. Kapsicum

SSCertifiable

Points: 6128

October 28, 2014 at 11:04 pm

#1755406

Basic Interview question.

Easy ..!!

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

Hugo Kornelis (10/28/2014)
One NULL is correct for a single-column constraint, not for composite. A better way to phrase it is that a UNIQUE constraint allows NULLs (as per ANSI standard), but treats NULL as a normal value that also has to conform to the uniqueness constraint (in violation of that same standard).

I wish BOL has clear wording like that. Or even more werbose, kind of

ALTER TABLE t ADD CONSTRAINT cu UNIQUE (a [,...n] ) means that for every row rNew being inserted into t (update is considered as delete + insert) there must hold

NOT EXISTS (SELECT NULL FROM t WHERE (t.a = rNew.a OR COALESCE(t.a, rNew.a) IS NULL) [AND ...n] )

g.maxfield Default port Points: 1471 More actions · Answer 7

I answered Primary Key won't allow nulls and Unique Key allows one null and I got it wrong - but it's showing correct in the answer?

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 8

Andre Ranieri

SSCrazy

Points: 2819

January 30, 2015 at 11:19 am

#1774432

+1 great question. Thanks!