Difference between Primary and unique key in SQL Server

Primary key vs. unique key

In this article we will see the difference between primary key and unique key with practical scenarios.

 

Primary Key	Unique Key
A table can have only one primary key	A table can have any number of unique keys
Does not allow null value so implicitly enforces not NULL constraint	Allows only one NULL value
Creates clustered index on the column	Creates non-clustered index on the columns

In the below script we will see the above points.

--table Create table PrimaryVsUniqueKey( id int constraint PK primary key, Col1 varchar(50) CONSTRAINT UniqueKey1 unique,Col2 varchar(50) constraint UniqueKey2 unique) --insert statements Insert into PrimaryVsUniqueKey values(1,'Ram','Laxman') --success Insert into PrimaryVsUniqueKey values(1,'Ajay','Vijay') --error as Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey' The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,'Ram','Vijay') --error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'.The statement has been terminated. Insert into PrimaryVsUniqueKey values(Null,'John','Cena') -- error as Cannot insert the value NULL into column 'id', table 'Practise.dbo.PrimaryVsUniqueKey'; column does not allow nulls. INSERT fails. The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,Null,Null) -- success Insert into PrimaryVsUniqueKey values(3,Null,'Test') -- Error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'. select * from PrimaryVsUniqueKey

Output: