Primary Key and Unique key difference in SQL Server

Posted from http://www.sqlservergeeks.com/primary-key-and-unique-key-difference-in-sql-server/

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

-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 are to enforce Uniquen ESS 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 is created on a null able column wherein A Unique key can is created on a null able column.

-There can is only one primary key on a table wherein a table can has multiple unique key defined on it.

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

Transact-SQL

CREATE table Tblcns (Sno INT not NULL, col1 varchar (+), col2 varchar) go--Add Unique key constraintalter TABLE TBLCN s add CONSTRAINT uq_cns_col1 unique (col1) go--add another unique constraintalter TABLE tblcns ADD CONSTRAINT uq_cns_col2 U Nique (col2) go--add a primary key constraintalter TABLE Tblcns Add CONSTRAINT Pk_cns_sno primary KEY (SNO) GO

Let ' s analyze the indexes using Sp_helpindex stored procedure.

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

Primary Key and Unique key difference in SQL Server