«
»

28 Oct

SQL Server – Difference between Primary key and Unique Key

Posted by Bhavesh Patel in SQL DDL Statements. Tagged: , , , , , , , , , , , , .

Difference between Primary key and Unique Key

I hope you know it, as this is one to most frequently asked question in interviews for DBA’s.

Both Primary Key and Unique Key constraints enforce uniqueness of the column (columns incase of composite key) on which they are defined. But there are key differences between them, see below:

Primary Key

  • Only one Primary key constrain per table
  • Primary Key is by default Clustered Indexed.
  • It does not allow NULL value.

– Create table Department with DeptID as Primary Key

CREATE TABLE Department(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(25)

)

– OR make DeptID of existing table Department as Primary Key

ALTER TABLE Department ADD CONSTRAINT pk_Department PRIMARY KEY (DeptID)

Unique Key

  • Multiple Unique constrains can be defined on the table.
  • Unique Key is by default Non-Clustered Indexed.
  • Unique Key constraint allows only one NULL value.

– Create table Employee with ProjectCode as Unique

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

ProjectCode INT UNIQUE,

)

– OR make ProjectCode of existing table Employee as Unique

ALTER TABLE Employee ADD CONSTRAINT IX_ProjectCode UNIQUE (ProjectCode)

Respond to this post