Difference between primary key and unique key in SQL Server

By
Difference between Primary Key  & Unique Key
Primary key & Unique key is one of the most useful word in SQL Server. Both key enforce to avoid duplicate value.Choosing a primary key is one of the most important part in Database.Its need because with the help of key you have got the row level accessibility means you can find row which you exactly want.I am here explain the difference in tabular format hope it will be helpful for you.

Primary key & Unique key SQL Server

Primary Key Unique Key
Definition
Primary key is uniquely  identify each row in a table.Primary key never accept null value. Unique also same as primary key.It uniquely identify each row in a table.Unique key accept only one null value.
How many key?
In a table only one primary key exist. More than one unique key are possible in a table. 
What about index?
In SQL server when we create a primary key in a table then by default its as a Cluster index. When we add a unique key then its by default a unique non cluster index.
Is combination of column make the primary and unique key?
Yes,Primary key apply on one column or more than one column. yes.Unique key also apply on one column or more than one column.
Syntax for creating the key in particular column
CREATE TABLE MyProject
(
 ProjectId INT NOT NULL PRIMARY KEY,
 ProjectName VARCHAR(100),
 IsActive bit
)
CREATE TABLE MyProject
(
 ProjectId INT NOT NULL PRIMARY KEY,
 ProjectName VARCHAR(100),
 IsActive bit
)
Syntax for creating the key in combination of  multiple column
CREATE TABLE MyProject
(
 ProjectId INT NOT NULL PRIMARY KEY,
 ProjectName VARCHAR(100),
 IsActive bit,
 CONSTRAINT [PK_MyProject_Combine] PRIMARY KEY  
(
[ProjectId] ASC,
[ProjectName] ASC
))
CREATE TABLE MyProject
(
 ProjectId INT NOT NULL PRIMARY KEY,
 ProjectName VARCHAR(100),
 IsActive bit,
 CONSTRAINT [UK_MyProject_Combine]
 UNIQUE  
(
[ProjectId] ASC,
[ProjectName] ASC
))
Add key after creation the table 
ALTER TABLE MyProject
ADD CONSTRAINT PK_MyProject PRIMARY KEY
(ProjectId)
ALTER TABLE MyProject
ADD CONSTRAINT UK_MyProject UNIQUE
(ProjectId).
Drop key syntax
ALTER TABLE MyProject
DROP CONSTRAINT PK_MyProject 
ALTER TABLE MyProject DROP CONSTRAINT UK_MyProject

1 comment: