Difference Between Primary Key and Unique Key In Sql Server

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:

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_CUSTOMERUNIQUE (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 TABLEdbo.Customer
DROP CONSTRAINT UK_CUSTOMER