none
SQL Server – Primary Key and a Unique Key

    Question

  • Hello Everyone,

    I would like to know What’s the Difference between a Primary Key and a Unique Key?

    Kate

    Thursday, July 26, 2012 3:10 AM

Answers

  • Primary Key:
    ...

    Having a primary key sort the data in an order.

    Technically, this is not true.  The data gets sorted in the order of the clustered index; in most cases the table will be clustered according to the primary key; however, it doesn't have to be that way.

    In addition to these, the primary key is the default target for foreign keys that reference the table, but is also possible to reference the table through a foreign key that targets a unique key; this is not a feature I use much.


    Thursday, July 26, 2012 11:50 AM
    Moderator

All replies

  • Hi Kate,

    Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another major difference is that primary key doesn’t allow NULLs, but unique key allows one NULL only.

    Nimol,
    Programmer
    Corbin Creative Databases, LLC, http://www.corbincreative.com

    Thursday, July 26, 2012 3:11 AM
  • Hi Kate,

    Greeting of the day,

    Primary Key:
    If you have a primary key:
    Duplicate values & null values are not allowed at all.
    By Default it creates a clustered Index.
    Having a primary key sort the data in an order.

    Unique [means different] Key:
    If you have a Unique Key:
    It allows only different values as like primary key but the difference is it allows one Null value.
    By Default it creates non Clustered Index.

    Difference between Clustered & Non Clustered Index.

    In common they use a binary tree structure.

    Clustered index uses key values.

    Non clustered index uses Pointers to locate or retrieve the data.

    Regards,

    Kalyan

    SQL DBA [VLBS]

    Thursday, July 26, 2012 4:02 AM
  • Here is a blog post by Pinal dave for you with examples.

    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thursday, July 26, 2012 9:50 AM
  • Primary Key:
    ...

    Having a primary key sort the data in an order.

    Technically, this is not true.  The data gets sorted in the order of the clustered index; in most cases the table will be clustered according to the primary key; however, it doesn't have to be that way.

    In addition to these, the primary key is the default target for foreign keys that reference the table, but is also possible to reference the table through a foreign key that targets a unique key; this is not a feature I use much.


    Thursday, July 26, 2012 11:50 AM
    Moderator