Difference Between Primary Key and Unique Key in SQL

Primary and unique key uniquely identifies each row in table but there are some subtle difference between them. here are some of them :

1) Unique key in a table can be null, at-least one but primary key can not be null in any table in relation database like MySQL , Oracle etc.

2) Primary key can be combination of more than one unique keys in same table.

3) There can be only one primary key per table in relation database e.g. MySQL, Oracle or Sybase but there can be more than one unique key per table.

4) Unique key is represented using unique constraint while primary key is created using primary key constraint in any table and it's automatically gets unique constraint.

5) Many database engine automatically puts clustered index on primary key and since you can only have one clustered index per table, its not available to any other unique key at same time.

Difference between Candidate Key vs Primary Key in MYSQL

Some Differences are given below.
 1) Both Primary and Candidate keys can uniquely identify records in a table on database.
 2) Both Primary and Candidate keys are has constraints UNIQUE and NOT NULL.
 3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.


Now from interview point of view here is difference between Candidate key and primary key in SQL table on point format for easy to remember :

1) There can be multiple Candidate keys in a table in relation database e.g. Oracle, MySQL, Sybase or MSSQL but only one primary key is permitted.
2) An example of Primary key and Candidate key can be ID and SSN number in a Employee table, Since both can identify each employee uniquely .They are candidate key and any one can become primary key. Now if you have to choose between them as primary key, I will go ID as primary key because SSN is sensitive information and may not be allow/not safe to use as String in queries as frequently as ID. Second reason of choosing ID over SSN as primary key can be use of ID as primary tracking ID within organization and its frequent use all over the place. Once you choose a primary key,
3) All candidate key  are like unique keys.