difference between primary key and unique key

Unique Key (UK): It's a column or a group of columns that can identify a uniqueness in a row.

Primary Key (PK): It's also a column or group of columns that can identify a uniqueness in a row.

So the Primary key is just another name for unique key, but the default implementation in SQL Server is different for Primary and Unique Key.

By Default:

  1. PK creates a Clustered index and UK creates a Non Clustered Index.
  2. PK is not null, but UK allows nulls (Note: By Default)
  3. There can only be one and only one PK on a table, but there can be multiple UK's
  4. You can override the default implementation depending upon your need.

It really depends what is your aim when deciding whether to create a UK or PK. It follows an analogy like "If there is a team of three people, so all of them are peers, but there will be one of them who will be a pair of peers: PK and UK has similar relation.". I would suggest reading this article: The example given by the author may not seem suitable, but try to get an overall idea.

http://tsqltips.blogspot.com/2012/06/difference-between-unique-key-and.html


Primary Key:

  • There can only be one primary key constraint in a table
  • In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
  • Primary Key is a unique key identifier of the record

Unique Key:

  • Can be more than one unique key in one table
  • Unique key can have NULL values
  • It can be a candidate key
  • Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"