Post reply

UNIQUE NOT NULL vs PRIMARY KEY

  • March 6, 2012 at 4:36 am

    #252537

    Hi,

    I am very curious to know the key difference between unique not null and primary key.:w00t:

  • March 6, 2012 at 4:42 am

    #1455196

    I can think of two. Is this for homework, or an interview?

    John

  • March 6, 2012 at 4:45 am

    #1455199

    homework..

    i want to know the basic performance difference between them.

  • March 6, 2012 at 4:48 am

    #1455202

    Most people here will be loath to do your homework for you if you don't show any evidence of having attempted to do so yourself. I find that search engines are handy for stuff like this.

    John

  • March 6, 2012 at 4:52 am

    #1455205

    Thank you for your reply.

    Sqlservercentral is best place to post this question.

    Kindly help me out.

  • March 6, 2012 at 5:01 am

    #1455206

    What differences have you found so far? Try and explain in your own words and you'll either get applause or explanations where you've gone wrong.


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • March 6, 2012 at 5:12 am

    #1455214

    deepikamm (3/6/2012)

    Thank you for your reply.

    Sqlservercentral is best place to post this question.

    Kindly help me out.

    Exactly right! Have you tried to google for the title you gave to the post? Most obviously not, as you wouldn't post the question. Do the homework man (or Lady)!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 6, 2012 at 5:13 am

    #1455215

    as far as i have understood,

    we are making unique key to behave as primary key by enforcing not null constraint on it.

    by default clustered index is created on a primary key, but for unique key non-clustered index is created by default.

  • March 6, 2012 at 5:16 am

    #1455216

    Please do not comment without knowing the fact that whether i have googled or not.

    i thought that the experts in sqlservercentral would give me a better clear answer thats y posted it here.

    sorry if am wrong!!

  • March 6, 2012 at 5:23 am

    #1455220

    Much better now!

    So, what else we can add:

    1. they differ in purpose:

    We are using PK for referential integrity (when paired with FK)

    While UNIQUE NOT NUL index(key) we use for data consistency and validation

    2. Replication will not work on tables without PK even if they have UNIQUE NOT NUL index

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • March 6, 2012 at 5:27 am

    #1455221

    I guess we can also use unique not null to enforce referential integrity

    sorry if i am wrong

  • March 6, 2012 at 5:42 am

    #1455225

    also a primary key cannot contain a NULL value, but a UNIQUE constraint can contain 1 row which is NULL

  • March 6, 2012 at 5:46 am

    #1455227

    deepikamm (3/6/2012)

    I guess we can also use unique not null to enforce referential integrity

    You can, yes. SQL is quite happy for a foreign key to reference a unique index, a unique constraint or a primary key.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 6, 2012 at 5:47 am

    #1455228

    anthony.green (3/6/2012)

    also a primary key cannot contain a NULL value, but a UNIQUE constraint can contain 1 row which is NULL

    Unless the column the unique constraint is defined on is declared not null (as was here)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 6, 2012 at 5:50 am

    #1455230

    Also, I don't think anyone's mentioned that you can have many unique indexes on a table, but only one primary key constraint.

    John

Viewing 15 posts - 1 through 15 (of 22 total)

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