SQL Server – Difference between Primary key and Unique Key

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: