Difference between Primary Key and Unique Key in Sql Server
In Sql server Primary key and Unique Key seems identical as both identify a unique and distinct record in the database, but Primary Key and Unique Key both are different in it’s Features and behavior so logical implementation is also different . In this article we will compare Primary Key and Unique Key.
Key difference between primary key and unique key
|PRIMARY KEY||UNIQUE KEY|
|Primary Key never allow and accept null values.||Unique Key can have only one null values.|
|Table can have only one Primary Key.||Table can have more than one Unique Key.|
|Primary Key is Clustered Index.||Unique Key is non-Clustered Index.|
|Primary Key Data in Database Table is Physically organized in the sequence because of Clustered Index.||Unique Key Data is not Physically organized in the sequence.|
|Primary Key can make foreign key in another table.||Unique Key can not be foreign key in another table.|
When to use Primary Key and Unique Key
Now think we have a DataBase of Air Lines company and we keep details of Passenger in tbl_Passenger table. Here we have scenario that we give Unique and Distinct Passenger Id for each and every Passenger , so we would like to give it as Primary Key which is guaranteed for us Unique , Distinct and not a nullable value. Now we have one another column named as Passport Number, here we want to be ensure that Passport Number for each and every Passenger must be Unique , in that case we will use Unique Key for Passport Number . Suppose we have another column named as Passenger Mobile Number and we want to be ensure that Passenger Mobile Number also must be Unique and Distinct , we can use another Unique key named as MobileNo . Now as we describe earlier about Primary Key VS Unique Key which is perfectly matching for our Database Table Criteria . Now we will see how to create Primary Key and Unique Key in Sql Server.
Create Primary Key and Unique Key in Sql Server
CREATE TABLE Passenger
PassengerID int PRIMARY KEY, -- Primary Key
Name varchar (50) NOT NULL,
PassportNo varchar (50) UNIQUE, -- Unique Key
MobileNo int UNIQUE, -- Unique Key
Resident_Address varchar (100) NULL
What is your opinion ?
Here I have tried my best to Demonstrate Different between Primary Key and Unique Key . I hope it will be very useful to you understand Primary Key and Unique Key . If you have any query you can comment or you can mail me.