Difference between Primary Key and Unique Key

 In SQL Server, we have two keys which distinctively or uniquely identify a record in the database. Both the keys seems identical, but actually both are different in features and behaviours. In this article, I would like to share the key difference between primary key and unique key. For more help about keys in SQL Server refer the article Different Types of SQL Keys.

Difference between Primary Key & Unique 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