Header Ads

Unique Key VS Primary Key

Difference Between Unique Key and Primary Key
Difference Between Unique Key and Primary Key
Unique KeyPrimary Key
LIMITA table can have more than one UNIQUE Key ColumnA table can have only one PRIMARY KEY Column
NULLAllows Null value. But only one Null value.It doesn’t allow Null value.Because of this we refer
PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT
INDEXBy default it adds a UNIQUE non-clusteredBy default it adds a clustered index
CREATE SYNTAXBelow is the sample example for defining a single column as a UNIQUE KEY column while creating a table:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL UNIQUE,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)
Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT UK_CUSTOMER UNIQUE(Id,FirstName)
)
Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(50)
)

Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY:
CREATE TABLE dbo.Customer
(
Id INT NOT NULL,
FirstName VARCHAR(100) NOT NULL,
LastName VARCHAR(100),
City VARCHAR(50),
CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id,FirstName)
)
ALTER SYNTAX
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:
ALTER TABLE dbo.Customer
ADD CONSTRAINT UK_CUSTOMER UNIQUE
Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:
ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id)
DROP SYNTAX
Below is the Syntax for dropping a UNIQUE KEY:
ALTER TABLE dbo.Customer
DROP CONSTRAINT UK_CUSTOMER
Below is the Syntax for dropping a PRIMARY KEY:
ALTER TABLE dbo.Customer
DROP CONSTRAINT PK_CUSTOMER

No comments:

Powered by Blogger.