Differences Between Primary Key and Unique Key

Click here for you tube video

Below are the major differences between Primary Key and Unique Key

1. Primary does allow NULLS but Unique Key allows one NULL value per column

2. There can be only one Primary Key per table but there can be multiple unique keys can be created on a table

3. Primary Key creates a CLUSTERED UNIQUE INDEX by default where as unique creates a NON CLUSTERED UNIQUE INDEX by default

Lets see this using few examples:

PRIMARY KEY:

Creating a table with a Primary Key on ID column

CREATE TABLE tblEmployee_PK(
ID INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Location VARCHAR(20) NOT NULL,
SSN_Number INT NULL,
PRIMARY KEY  (ID)

);


1. PK does not allow NULLs

INSERT INTO tblEmployee_PK VALUES (1,'smith','London','9876543')

INSERT INTO tblEmployee_PK VALUES (NULL,'test1','test','765433')

When we insert the second record it will through below error

Msg 515, Level 16, State 2, Line x
Cannot insert the value NULL into column 'ID', table 'SqlWithRaviMartha.dbo.tblEmployee_PK'; column does not allow nulls. INSERT fails.

The statement has been terminated.

2. Table can not have more than one Primary Key

ALTER TABLE tblEmployee_PK

ADD PRIMARY KEY (SSN_Number);

When we try to create one more PK, it gives below error

Msg 1779, Level 16, State 0, Line X
Table 'tblEmployee_PK' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 41

Could not create constraint or index. See previous errors.

3. Table can not have more than one Primary Key

UNIQUE KEY:

1. A Table can have multiple Unique Keys

Below is the statement which creates a table with 2 unique keys on ID and SSN_Number columns

CREATE TABLE tblEmployee_UK(
ID INT NULL,
Name VARCHAR(20) NOT NULL,
Location VARCHAR(20) NOT NULL,
SSN_Number INT NULL,
CONSTRAINT tblEmployee_UK_ID UNIQUE(ID),
CONSTRAINT tblEmployee_UK_SSN_Number UNIQUE(SSN_Number)

);

2. Unique Key (UK) creates a Non Clustered Index by Default




3. Unique Key (UK) allows only One NULL value per column on which its defined

INSERT INTO tblEmployee_UK Values (NULL,'smith','London','1234578') 
INSERT INTO tblEmployee_UK Values (NULL,'Scott','Dallas','123576380') 

When you execute the second statement, it throw below error:

INSERT INTO tblEmployee_UK Values (456,'Adams','Scotland',NULL) 

INSERT INTO tblEmployee_UK Values (365,'John','Newyork',NULL) 

Similar error occurs when we run the second insert statements because when two NULLs are  inserted in a column then it can not be unique and hence the Unique constraint restricts the NULL value entering in to the table.

 2. What if a CLUSTERED UNIQUE INDEX already present in the table, if we create a Primary Key then NON CLUSTERED UNIQUE INDEX will be created

CREATE TABLE tblEmployee_CI_PK(
ID INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Location VARCHAR(20) NOT NULL,
SSN_Number INT NOT NULL,

);

Creating a Clustered Unique Index

CREATE UNIQUE CLUSTERED INDEX tblEmployee_CI_PK_ID

ON dbo.tblEmployee_CI_PK (ID);  

Primary Key will be created using Non Clustered Unique Index as there is a Clustered Unique Index is already present.