What is the difference between primary key and unique key and why one should use unique key if it allows only one null?

The main difference between a primary key and unique key is that there can only be one primary key, whereas a unique key can be any constraint that can identify a uniqueness in a row. There can even be more than one unique key, and it can even be the candidate key.

Another difference between them is that a primary key does not include Null values whereas a unique key can.

ANSI SQL allows columns with unique keys to have multiple nulls, however, Microsoft SQL Server only allows only a single null. This happens because NULL is just another value, and NULL=NULL is true. This is actually a side-effect of the way Microsoft implemented the comparison for uniqueness.

However, one can create the UNIQUE constraint as a filtered index in order to bypass this restriction. This will apply the unique constraint only on rows where the column is not NULL, hence allowing any number of rows with NULL.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.