Thursday, October 20, 2011

Difference between Primary key and Unique Key with example

Posted by Rahul Kharde at 11:01 PM
PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.

Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).

Primary Key
Unique Key
It will not accept null values
One and only one Null values are accepted.
There will be only one primary key in a table
More than one unique key will be there in a table.
Clustered index is created in Primary key
Non-Clustered index is created in unique key.
Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.
Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Example

CREATE TABLE BOOK
(
    BOOKID INT NOT NULL PRIMARY KEY,
    BOOKName VARCHAR(100)
)
GO

ALTER TABLE BOOK ADD CONSTRAINT UK_BookName UNIQUE(BOOKName)
GO

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(1,null)

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(2,'ASP')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(3,'C')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(4,'C++')

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(5,'PHP')


BOOKID
BOOKName
1
NULL
2
ASP
3
C
4
C++
5
PHP

If you try again to insert null value into table it shows an error message

INSERT INTO BOOK(BOOKID,BOOKName)VALUES(6,null)

Cannot insert the value NULL into column 'BOOKName', table 'Testing.dbo.BOOK'; column does not allow nulls. INSERT fails.

The statement has been terminated.


If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

4 comments:

prosanilin on October 21, 2011 at 4:13 AM said...

Thanks for the scholarly post Rahul.
College Graduate Jobs


christian jerick Go on December 21, 2015 at 6:51 PM said...

Practical suggestions . I am thankful for the analysis ! Does someone know if I might get ahold of a sample IRS W-3 document to work with ?


dktechnologies Pvt Ltd on March 2, 2016 at 10:27 PM said...

Thanks to give useful information for sql contraints.

Full Form Directory


Jagna Co Kalani on November 3, 2017 at 6:41 AM said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.


Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top