Primary vs. Unique Key

Both PRIMARY KEY and UNIQUE KEY enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined.  Also these key’s can Uniquely identify each row in database table.
Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:


one more major difference is primary key can be referred as foreign key and unique key can not refer as foreign key.

	PRIMARY KEY	UNIQUE KEY
NULL	It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT	Allows Null value. But only one Null value.
INDEX	By default it adds a clustered index	By default it adds a UNIQUE non-clustered index
LIMIT	A table can have only one PRIMARY KEY Column[s]	A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAX	Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table: CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id,FirstName) )	Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table: CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT UK_CUSTOMER UNIQUE (Id,FirstName) )
ALTER SYNTAX	Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key: ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)	Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created: ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMER UNIQUE (Id)
DROP SYNTAX	Below is the Syntax for dropping a PRIMARY KEY: ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER	Below is the Syntax for dropping a UNIQUE KEY: ALTER TABLE dbo.Customer DROP CONSTRAINT UK_CUSTOMER