Thursday, May 23, 2013

Difference between Primary key and Unique Key in Sql


Difference between Unique key and Primary key

A UNIQUE constraint and PRIMARY key both are similar and it provide unique enforce uniqueness of the column on which they are defined.
Some are basic differences between Primary Key and Unique key are as follows.

Primary key
  1. Primary key cannot have a NULL value.
  2. Each table can have only single primary key.
  3. Primary key is implemented as indexes on the table. By default this index is clustered index.
  4. Primary key can be related with another table's as a Foreign Key.
  5. We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value. 
Unique Constraint
  1. Unique Constraint may have a NULL value.
  2. Each table can have more than one Unique Constraint.
  3. Unique Constraint is also implemented as indexes on the table. By default this index is Non-clustered index.
  4. Unique Constraint can not be related with another table's as a Foreign Key.
  5. Unique Constraint doesn't supports Auto Increment value.
Define Primary and Unique Key

Create table Student
(
StuId int primary key, ---- Define Primary Key
StuName varchar(50) Not Null,
ContactNo int Unique --- Define Unique Key
)
Insert some data in table


Now you will see above fig. StuId can not have NULL value but ContactNo can have NULL value.