Unique Key VS Primary Key
Difference Between Unique Key and Primary Key
Unique Key | Primary Key | |
LIMIT | A table can have more than one UNIQUE Key Column | A table can have only one PRIMARY KEY Column |
NULL | Allows 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 |
INDEX | By default it adds a UNIQUE non-clustered | By default it adds a clustered index |
CREATE SYNTAX | Below 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: