Saturday, April 16, 2011

Diffrence between unique and primary key

Primary key:

Uniquely identified each row in Table is called primary key. One table can have only one primary key (which can be combination of one or more than one columns), primary key can not allow null values and duplicate value. it is default cluster indexes.

Unique key:

Uniquely identified each row in Table is called uniquely key.Unique key does not allow duplicate values.Unique key allows only one null value in a column of table in which you have define unique key constraint.
Primary Key does not allow null values where as unique constraint allow ‘single’ null value.

By default, on Primary Key there is Clustered Unique Index. But on Unique Key there is Unique Non Clustered Index, by default.

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint 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.


  1. Hello Arun,

    As you stated that "One table can have more then one primary key". Please correct it, each table can have only ONE primary key.

    Thanks & Regards,

  2. Hello David,

    Yes I had made a mistake. I will correct this.

    Thanks for your kind cooperation.

    Thanks and regards
    Arun Ladha

  3. Hello Arun,

    My pleasure...