Difference between Primary key and unique key in SQL Server?

 Posted by Sriramnandha on 6/3/2012 | Category: Sql Server Interview questions | Views: 2838 | Points: 40
Answer:

1)Primary Key by definition cannot be null, where as unique key can accept null values but if the unique key is defined on a column which is not null , then this unique key can also be used as an alternate primary key functionality to identify unique rows in a table.

2)By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table

3)Also by default Primary key is created as clustered index and unique key is created as non clustered index.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Neeraaj.Sharma on: 7/2/2013 | Points: 10
just adding the explanation to above Answer

1)Primary Key by definition cannot be null,
Means if you have specified primary on column(s) then those column wont accept null values
and primary key create column not null and if you have existing table and on column(s) you want to add primary key constraint then those column has to be not null.
primary key constraint can be on one or multiple column.

unique key can accept null values,
Means you have implemented unique key constraint on column then one unique key`s row(s)
can accept null value ONE AND ONLY ONE NOT MORE THAN THAT.


3)Also by default Primary key is created as clustered index and unique key is created as non clustered index
means :
If there is no cluster index already defied on table then only primary key creates CLUSTERED INDEX
else primary key create NON clustered index, however you can make primary key with
non cluster index as well.
and you can also create unique key with CLUSTERED INDEX as well,

ref : http://www.tutorialsqlserver.com/Create/Create-Primary-key-In-Sql-Server.htm



Posted by: Neeraaj.Sharma on: 7/2/2013 | Points: 10
just adding the explanation to above Answer

1)Primary Key by definition cannot be null,
Means if you have specified primary on column(s) then those column wont accept null values
and primary key create column not null and if you have existing table and on column(s) you want to add primary key constraint then those column has to be not null.
primary key constraint can be on one or multiple column.

unique key can accept null values,
Means you have implemented unique key constraint on column then one unique key`s row(s)
can accept null value ONE AND ONLY ONE NOT MORE THAN THAT.


3)Also by default Primary key is created as clustered index and unique key is created as non clustered index
means :
If there is no cluster index already defied on table then only primary key creates CLUSTERED INDEX
else primary key create NON clustered index, however you can make primary key with
non cluster index as well.
and you can also create unique key with CLUSTERED INDEX as well,

ref : http://www.tutorialsqlserver.com/Create/Create-Primary-key-In-Sql-Server.htm



Login to post response