Difference between Primary key and Unique key

var gaJsHost = ((“https:” == document.location.protocol) ? “https://ssl.” : “http://www.”);
document.write(unescape(“%3Cscript src='” + gaJsHost + “google-analytics.com/ga.js’ type=’text/javascript’%3E%3C/script%3E”));
try {
var pageTracker = _gat._getTracker(“UA-11608194-1”);
pageTracker._trackPageview();
} catch(err) {}

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


Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

30 Comments

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading