Monday, October 5, 2009

Difference between Primary key and Unique key



Both Primary key and Unique key enforce uniqueness of the column on which they are defined but still there are some differences between them. The differences are defined below:-

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.

Suppose there is a table named tbl_primarykey whose structure is given below:-

CREATE TABLE tbl_primarykey (studentid INT NOT NULL PRIMARY KEY, Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here studentid is defined as the primary key. Now since primary key is already defined on the column studentid, no other primary key can be defined on the table tbl_primarykey

Again, suppose there is another table named tbl_UniqueIndex whose structure is given below:-

CREATE TABLE tbl_UniqueIndex(studentid INT NOT NULL UNIQUE , Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here Unique constraint is defined on the column studentid. We can also add another unique constraint on another column studentname of the table tbl_UniqueIndex.

ALTER TABLE tbl_UniqueIndex ADD CONSTRAINT Const_Unique UNIQUE(studentname)

The above query shows that there can be multiple unique constraints can be defined on a single table but on a single table only one primary key can be defined.

2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message. 

For example, if we try to insert NULL value into the column studentid of the table tbl_primarykey, we get the following error:-

INSERT INTO tbl_primarykey (studentid,Studentname,email)
VALUES(NULL,'Vivek' ,'vivek@mail.com')

Error:-

 


On the other hand if we want to insert NULL values in the column on which Unique constraint is defined we can do it.

For example, we can insert the null value in the column studentname of the table tbl_UniqueIndex.

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(1,NULL,'abc@mail.com')

Note:- We can only insert one NULL value inside the column on which a Unique Constraint is defined.

For example, if we try to insert more than one NULL value in the column Studentname of the table tbl_UniqueIndex, we get the following error:-

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(2,NULL,'virender@mail.com')

Error:-




3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.

If we check all the indexes made on the table tbl_primarykey and tbl_UniqueIndex, then we can found out that a clustered index is created by default when a Primary key is created and a Non_Clustered Index is created by default when a Unique Constraint is created on the table.

To check the indexes made on the table tbl_primarykey, we can use the following query:-

sp_helpindex tbl_primarykey




To check the indexes made on the table tbl_UniqueIndex, we can use the following query:-

sp_helpindex tbl_UniqueIndex





You can also get more information about Primary Key and Unique Constraint from the link given below:-



DMCA.com

7 comments:

  1. Hi Vivek,
    Its good article. I would like to some question regarding the above topic.
    1.Clustered and non clustered index.
    2.Does defining unique key in the table other than the primary key causes some loss in performance.
    3.And is it usefull to use unique key costrained other than by the requirement of design to do so.

    ReplyDelete
  2. Hi
    Thanks for reading the article and writing the valuable comments.
    Friend, your first question is about clustered index and non clustered index.

    Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted everytime whenever a new value is inserted or a value is updated in the column on which it is defined.In a table only one clusterd index is possible.
    Non Clustered Index:- In case of Non clustered index it create a sperate list of key values (or created a table of pointers) which points towards the location of the datain the datapages.In a table more than one non clustered index is possible.

    Defining unique key in the table other than the primary key will cause performance loss only when we dont use the unique key since by default non clustered index is made when we define the unique key.But it will increase the performance if we defined the unique key on the column when it is consists on the coulmn or columns which are frequently used in the "Where" clause of the Select Sql statement since it is not neccessary that we use the primary key columns in every select statement.
    Unique key with Not NULL constraint can be very useful if we use it in the table where there is no primary key is defined.Also Unique key help in faster retreival of the data from the table.

    Regards
    Vivek

    ReplyDelete
  3. Nice tutorials Vivek keep it up

    ReplyDelete
  4. Nice Tutorials Vivek ----ALOK

    ReplyDelete
  5. Thanks Alok for your response...:-)

    ReplyDelete
  6. Very nice and simple tutorial. Thanks Vivek----OM

    ReplyDelete