locked
Difference Primary Key & Unique key? RRS feed

  • Question

  • Please tell difference between Primary key & Unique key, Apart from the below difference?

    -- Primary key can't have null whereas unique key can have a single null value also.

    -- Be default clustered index comes on primary key.

    ?


    Thanks!! http://gurunguns.wordpress.com
    Saturday, June 26, 2010 10:02 AM

Answers

  • I think that apart from the possibility for NULLs, and the fact that you can only have one Primary Key (per table) it is a matter of convention.

    Out of all the candidate keys, you would typically choose one unique key and declare that as the Primary Key. All the other candidate keys you would declare as Unique Constraints.

    It is a matter of taste. The Primary Key could be the key that you would typically use to identify the row, or it could be the key that you would typically join the table on. My personal preference is to always join on the Primary Key.

    So in the following example, I would choose design 1 and not design 2, although that would be perfectly fine as well.

     

    -- Design 1: Primary Key on the "JOIN column"
    CREATE TABLE Users
    (user_id int     PRIMARY KEY CLUSTERED
    ,name    varchar(50) UNIQUE
    )
    
    -- Design 2: Primary Key on the "Search column"
    CREATE TABLE Users
    (user_id int     UNIQUE CLUSTERED
    ,name    varchar(50) PRIMARY KEY
    )
    

    By the way: if you have declared a Primary Key, and you have not declared any constraint to be "CLUSTERED", then the engine will generate a unique clustered index on the Primary Key. If you have no Primary Key (or explicitely declared it to be NONCLUSTERED), but you do have a Unique Constraint, then that will trigger a unique clustered index creation.

    -- 

    Gert-Jan

    • Proposed as answer by HunchbackMVP Saturday, June 26, 2010 1:55 PM
    • Marked as answer by GurunGuns Monday, June 28, 2010 3:34 AM
    Saturday, June 26, 2010 10:17 AM

All replies

  • I think that apart from the possibility for NULLs, and the fact that you can only have one Primary Key (per table) it is a matter of convention.

    Out of all the candidate keys, you would typically choose one unique key and declare that as the Primary Key. All the other candidate keys you would declare as Unique Constraints.

    It is a matter of taste. The Primary Key could be the key that you would typically use to identify the row, or it could be the key that you would typically join the table on. My personal preference is to always join on the Primary Key.

    So in the following example, I would choose design 1 and not design 2, although that would be perfectly fine as well.

     

    -- Design 1: Primary Key on the "JOIN column"
    CREATE TABLE Users
    (user_id int     PRIMARY KEY CLUSTERED
    ,name    varchar(50) UNIQUE
    )
    
    -- Design 2: Primary Key on the "Search column"
    CREATE TABLE Users
    (user_id int     UNIQUE CLUSTERED
    ,name    varchar(50) PRIMARY KEY
    )
    

    By the way: if you have declared a Primary Key, and you have not declared any constraint to be "CLUSTERED", then the engine will generate a unique clustered index on the Primary Key. If you have no Primary Key (or explicitely declared it to be NONCLUSTERED), but you do have a Unique Constraint, then that will trigger a unique clustered index creation.

    -- 

    Gert-Jan

    • Proposed as answer by HunchbackMVP Saturday, June 26, 2010 1:55 PM
    • Marked as answer by GurunGuns Monday, June 28, 2010 3:34 AM
    Saturday, June 26, 2010 10:17 AM
  • 1)We can have more than one UNIQUE constraint per table.
    2)UNique constraint will default create NON CLUSTERED INDEX.
    3)Unique constrint will accept NULL just for once.
    4)A table can have only one primary key.
    5)Primary key is a combination of NOT NULL and Unique values.

    Thanks

    Ramesh.M

    • Proposed as answer by Ai-hua Qiu Monday, June 28, 2010 4:00 AM
    Saturday, June 26, 2010 10:18 AM