Home > Database, SQL Server > Difference between UNIQUE constraint and PRIMARY key

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.

About these ads
Categories: Database, SQL Server
  1. Rubinaalexmary
    March 1, 2007 at 10:15 am | #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.

    • Hyma
      February 1, 2012 at 6:09 pm | #2

      thank you ,very helpful

    • MANISH PANDEY
      March 21, 2013 at 7:23 am | #3

      I THINK IT IS RIGHT

    • pooja maheshwari
      July 25, 2013 at 5:17 pm | #4

      thank you
      a comparison to not null key would also be helpful

  2. April 17, 2007 at 7:12 am | #5

    It is good try to collect more points

  3. Sumana
    August 6, 2007 at 11:27 am | #6

    Hi,

    Good explanation

  4. Truong Trung Trang
    August 31, 2007 at 12:57 am | #7

    Very clear and helpful explanation! Thanks a lot

  5. jayashree
    December 19, 2007 at 6:56 am | #8

    good

  6. Deepthi
    January 5, 2008 at 10:51 am | #9

    If possible,pls include a detailed explanation…

  7. chaitanya
    February 12, 2008 at 7:03 am | #10

    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)

    • vits
      March 5, 2013 at 3:18 pm | #11

      thanks a lot. i was looking for this info….

  8. patar
    February 22, 2008 at 1:36 am | #12

    Very good. Many thanks

  9. Sunil Yadav
    February 29, 2008 at 11:42 pm | #13

    Very good explanation difference between primary key and unique key

  10. Aravind
    May 13, 2008 at 6:59 am | #14

    its very good article..
    thks a lot

  11. Aravind
    May 13, 2008 at 7:01 am | #15

    thks a lot to Rubinaalexmary

    regards
    achiever.

  12. Kanhaiya
    July 2, 2008 at 2:13 pm | #16

    Simly Great….

  13. perumal
    July 8, 2008 at 2:15 pm | #17

    excellent, thanks a lot….

  14. AIDSTER
    July 17, 2008 at 5:29 am | #18

    ty so much

  15. Sunil Bhati
    July 31, 2008 at 9:24 am | #19

    Nice arctical ……

  16. August 20, 2008 at 5:39 am | #20

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

  17. nirmala
    September 15, 2008 at 3:56 pm | #21

    Good explanation..
    txs
    nirmala

  18. September 16, 2008 at 11:28 am | #22

    good explanation

  19. September 16, 2008 at 11:28 am | #23

    Are govind chuudaraa

  20. September 16, 2008 at 11:29 am | #24

    jaheer gaadiki kuuda cheppu

  21. September 25, 2008 at 5:02 pm | #25

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

  22. Prashant
    September 26, 2008 at 8:52 am | #26

    Helped a lot..

  23. Ali
    October 29, 2008 at 5:12 am | #27

    nice, useful for getting knowledge from the scratch.

  24. sourav
    February 9, 2009 at 9:00 am | #28

    Please give an example.

  25. debashish
    April 13, 2009 at 7:14 am | #29

    thanks, very good explanation

  26. Asim
    August 28, 2009 at 3:58 am | #30

    Very Well Explanation.
    Thanks for your help

  27. Sivakumar
    October 6, 2009 at 4:42 am | #31

    Very nice explanation

  28. Asghar
    October 18, 2009 at 5:37 pm | #32

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

  29. Inoocent
    November 12, 2009 at 9:14 am | #33

    tadeee pan no kukar khan … saleyooo…!!!

  30. raj dhakad
    January 2, 2010 at 11:46 am | #34

    thnx
    nice explaination…..

  31. January 2, 2010 at 11:48 am | #35

    thnx
    nic explaination…..

  32. neenu
    May 18, 2010 at 5:34 pm | #36

    nice xplanation!!!

  33. swarnima
    July 11, 2010 at 1:35 pm | #37

    good!! but….. need more explanation.

  34. Parthiv
    July 26, 2010 at 4:09 am | #38

    Thanx a lot….

  35. krishna
    September 1, 2010 at 12:29 pm | #39

    Unique key constrain nothing but when we apply unique key column on any particular table or column, that column does not accept duplicate value, it can accept Null values if we cross the condition on column it will an error message,

    but we can have more than one UNIQUE constraint per table.

    Primary key nothing but not null constraint and unique column when we apply primary key column on any particular table or column, that column can not accept null values and it can not accept duplicate values mean time it arranges the data in ascending order,
    but we can not have more than one primary key constraint per table

  36. sanket berde
    April 20, 2011 at 8:20 pm | #40

    thank you….

  37. abhishek
    May 10, 2011 at 6:47 am | #41

    thanks krishna..!

  38. Lokesh
    June 6, 2011 at 9:27 pm | #42

    very Good Explanation helps a lot…
    thank u

  39. Vijitha Epa
    September 9, 2011 at 10:13 am | #43

    A good explaination. not messy with so many explanations and examples. as the first level of explanation. Good and summarised. Thanks

  40. Kuldeep Bansal
    December 24, 2011 at 3:58 pm | #44

    This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another good collection of sql keys over internet.
    please check out this link…

    http://mindstick.com/Articles/d837d16c-d483-4948-ad18-41b405c011bd/?Important%20SQL%20Keys

  41. mallikarjuna
    December 26, 2011 at 6:52 am | #45

    good explanation thaq.

  42. kalai
    March 17, 2012 at 10:59 am | #46

    can any one help me to find out how to delete a duplicat values in a table

  43. maya
    August 15, 2012 at 6:00 am | #47

    what is cluster index and non cluster index?

  44. Jay
    August 16, 2012 at 7:23 am | #48

    Very well Explained…Thank You !

  45. Swathika
    August 18, 2012 at 6:44 am | #49

    Hi Guys,
    Please let me know,which one is the faster while retrieving the data.

  46. rajath
    October 24, 2012 at 11:37 am | #50

    thanks was helpfull

  47. kalyan
    November 1, 2012 at 6:59 am | #51

    Thanks it was helpful

  48. Anil Kumar
    August 3, 2013 at 3:53 pm | #52

    nice

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: