Primary Key and Unique Key difference in SQL Server

Primary and Unique Key both enforce uniqueness of columns on which they are defined. Then where do they differ? They differ in following way

–          A primary key doesn’t allow null value wherein a unique key allows one null value.

–          A primary keys purpose is to uniquely define a row in a table wherein unique keys purpose is to enforce uniqueness on a column(s).

–          A primary key is implemented by a default unique clustered index on column(s) wherein unique key is implemented by a default unique non clustered index on column(s).

–          A primary key can’t be created on a null able column wherein a unique key can be created on a null able column.

–          There can be only one primary key on a table wherein a table can have multiple unique key defined on it.

Let’s create a table with a primary key and a unique key constraint.

CREATE TABLE tblCns (sno INT NOT NULL, col1 VARCHAR(100),col2 VARCHAR(100))
GO
-- Add unique key constraint
ALTER TABLE tblCns ADD CONSTRAINT uq_Cns_col1 UNIQUE(col1)
GO
-- Add another unique constraint
ALTER TABLE tblCns ADD CONSTRAINT uq_Cns_col2 UNIQUE(col2)
GO
-- Add a primary key constraint
ALTER TABLE tblCns ADD CONSTRAINT pk_Cns_sno PRIMARY KEY(sno)
GO

Let’s analyze the indexes using sp_helpindex stored procedure.

There are two unique key and a primary key. Attempt to create a second primary key will result in an error. The primary key is implement by a unique clustered index and unique key is implemented by a unique non clustered index.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook