Primary key vs Unique key

  • 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.

  • 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.

  • 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

  • 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.

  • Basic Interview question.

    Easy ..!!

  • 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] )

  • 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?

  • +1 great question. Thanks!

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

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