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:
- PK creates a Clustered index and UK creates a Non Clustered Index.
- PK is not null, but UK allows nulls (Note: By Default)
- There can only be one and only one PK on a table, but there can be multiple UK's
- 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 addsNOT 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 haveNULL
values and therefore may not be considered "unique"