Differences Between Primary Key and Unique Constraint

 

If you are reading this article, you should have read our articles “What is Primary Key And Foreign Key” and “What is Unique Constraint“.

After I told these two articles, what might be the difference between the two of them. You can find the differences below.

 

Primary Key

  • Primary Key can not contain null value
  • A table can only have one primary key.
  • Sparse columns can not be used as part of the primary key.
  • By default, when you set a column as a primary key, that column is created as a clustered index, and the table is physically sorted according to this column (you can browse the Clustered Index and Non Clustered Index Differences named nomenclature).
  • Primary Key can be associated with another table Foreing Key.
  • Self-incrementing identity can be set.

 

Unique Constraint

  • It can contain 1 Null value. Because 2nd Null value corrupts singularity. You will get an error like the following.

 

Violation of Unique Key Constraint ‘x’. Cannot insert duplicate key in object ‘x’.

  • Each table can have more than one Unique Constraint
  • By default, if you put a unique constraint on a column, a nonclustered index created on that column.
  • Self-incrementing identity can not be set.