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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Imp SQL Difference, Interview Questions, SQL Primary Key Constraint, SQL Server and tagged . Bookmark the permalink.

30 Responses to Difference between Primary key and Unique key

  1. Anonymous says:

    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.

  2. Vivek Johari says:

    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

  3. Raman says:

    Nice tutorials Vivek keep it up

  4. Vivek Johari says:

    Thanks Raman…….

  5. Anonymous says:

    Nice Tutorials Vivek —-ALOK

  6. Vivek Johari says:

    Thanks Alok for your response…:-)

  7. Anonymous says:

    Very nice and simple tutorial. Thanks Vivek—-OM

  8. Pingback: Haunted House in Albany NY

  9. Pingback: Torch onion address

  10. Pingback: home remodeling in Johnson County Kansas

  11. Pingback: venta de domos

  12. Pingback: w88top.com

  13. Pingback: Fred

  14. Pingback: i99bet

  15. Pingback: ดูบอลสด

  16. Pingback: Darknet

  17. Pingback: w88

  18. Pingback: Cryptonia Market

  19. Pingback: Darknet

  20. Pingback: http://www.goldenspringspharmacy.com/shop/buy-hydrocodone-pills-online/

  21. Pingback: Sex toys

  22. Pingback: tesla model 3 review

  23. Pingback: https://www.olx.pl/oferta/handlowiec-seo-sem-funkymedia-CID4-IDC4p2D.html

  24. Pingback: pelangi qq

  25. Pingback: www.indoqqpoker.site

  26. Pingback: www.jaguarqq1.site

  27. Pingback: Aromatherapy

  28. Pingback: https://onlinecasinos77lithuania.com/

  29. Pingback: ignou mba project

  30. Pingback: คาสิโน

Leave a Reply