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.

Let’s analyze the indexes using sp_helpindex stored procedure.

Primary Key and Unique Key difference in SQL Server

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.


Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook