Difference between UNIQUE constraint and PRIMARY key

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

Possibly related posts: (automatically generated)

This entry was posted on November 22, 2006 at 7:50 am and is filed under Database and SQL Server. You can feed this entry. You can leave a response, or trackback from your own site.

28 Responses

  1. The basic differences between Primary Key and Unique key are as follows.

    1) By default Primary Key will generate Clustured Index
    whereas Unique Key will Generate Non-Clustured Index.

    2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t
    have duplicate values or any NUll
    Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL
    Server It can have only one NULL

    3) A table can have only one PK but It can have any number of UNIQUE Key.

    Rubinaalexmary - March 1, 2007 at 10:15 am

  2. It is good try to collect more points

    hemadri - April 17, 2007 at 7:12 am

  3. Hi,

    Good explanation

    Sumana - August 6, 2007 at 11:27 am

  4. Very clear and helpful explanation! Thanks a lot

    Truong Trung Trang - August 31, 2007 at 12:57 am

  5. good

    jayashree - December 19, 2007 at 6:56 am

  6. If possible,pls include a detailed explanation…

    Deepthi - January 5, 2008 at 10:51 am

  7. Primary Key does not allow null values where as unique constraint allow ’single’ null value.

    A table can have only single Primary Key where as it can have multiple unique constraints ( max 16)

    chaitanya - February 12, 2008 at 7:03 am

  8. Very good. Many thanks

    patar - February 22, 2008 at 1:36 am

  9. Very good explanation difference between primary key and unique key

    Sunil Yadav - February 29, 2008 at 11:42 pm

  10. its very good article..
    thks a lot

    Aravind - May 13, 2008 at 6:59 am

  11. thks a lot to Rubinaalexmary

    regards
    achiever.

    Aravind - May 13, 2008 at 7:01 am

  12. Simly Great….

    Kanhaiya - July 2, 2008 at 2:13 pm

  13. excellent, thanks a lot….

    perumal - July 8, 2008 at 2:15 pm

  14. ty so much

    AIDSTER - July 17, 2008 at 5:29 am

  15. Nice arctical ……

    Sunil Bhati - July 31, 2008 at 9:24 am

  16. its good, but pls try to give the detailed description about the differences

    neha - August 20, 2008 at 5:39 am

  17. Good explanation..
    txs
    nirmala

    nirmala - September 15, 2008 at 3:56 pm

  18. good explanation

    vithal - September 16, 2008 at 11:28 am

  19. Are govind chuudaraa

    vithal - September 16, 2008 at 11:28 am

  20. jaheer gaadiki kuuda cheppu

    vithal - September 16, 2008 at 11:29 am

  21. Rubinaalexmary thank you very much for your precision made on March 1, 2007

    DynV - September 25, 2008 at 5:02 pm

  22. Helped a lot..

    Prashant - September 26, 2008 at 8:52 am

  23. nice, useful for getting knowledge from the scratch.

    Ali - October 29, 2008 at 5:12 am

  24. Please give an example.

    sourav - February 9, 2009 at 9:00 am

  25. thanks, very good explanation

    debashish - April 13, 2009 at 7:14 am

  26. Very Well Explanation.
    Thanks for your help

    Asim - August 28, 2009 at 3:58 am

  27. Very nice explanation

    Sivakumar - October 6, 2009 at 4:42 am

  28. Its very good and very clear explanation. Thank you so much

    Asghar - October 18, 2009 at 5:37 pm

Leave a Reply

«
»