Difference between Primary Key & Unique Key

Define Primary key and Unique key

 CREATE TABLE Employee 
 (
 EmpID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 MobileNo int UNIQUE, --define unique key
 Salary int NULL
 ) 

PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.

Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).

Primary Key	Unique Key
It will not accept null values	One and only one Null values are accepted.
There will be only one primary key in a table	More than one unique key will be there in a table.
Clustered index is created in Primary key	Non-Clustered index is created in unique key.
Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.	Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Example

CREATE TABLE BOOK

(

BOOKID INT NOT NULL PRIMARY KEY,

BOOKName VARCHAR(100)

)

GO

ALTER TABLE BOOK ADD CONSTRAINT UK_BookName UNIQUE(BOOKName)

GO

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(1,null)

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(2,'ASP')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(3,'C')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(4,'C++')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(5,'PHP')

BOOKID	BOOKName
1	NULL
2	ASP
3	C
4	C++
5	PHP

If you try again to insert null value into table it shows an error message

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(6,null)

Cannot insert the value NULL into column 'BOOKName', table 'Testing.dbo.BOOK'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Primary Key	Unique Key
Primary Key can't accept null values.	Unique key can accept only one null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	By default, Unique key is non-clustered index.
We can have only one Primary key in a table.	We can have more than one unique key in a table.
Primary key can be made foreign key into another table.	Unique key can't be made foreign key into another table.