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 |
Nice way of presentation.
ReplyDelete