Primary Key - SQL Server

Primary Key constraint

·         Primary Key Constraint is used to restrict duplicate values. While inserting data into a particular table, PRIMARY KEY constraint uniquely identifies each record.

·         Primary Key has to apply for a particular column in a table or can apply for group of columns.

·         If you apply for a single column, you can't enter one value more than once in that column.

How to define primary key

1)  Syntax:

CREATE TABLE <tbl_Name>                                                       

(                                                             

Col1 Datatype NOT NULL CONSTRAINT <PrimaryKeyID> PRIMARY KEY,

Col2 Datatype

)

Example

CREATE TABLE tbl_Employees

(

EmpID INT NOT NULL CONSTRAINT pk_Emp_ID PRIMARY KEY,

EmpName VARCHAR(50)

)

2)  Syntax:

CREATE  TABLE <Tbl_Name> (                                                    

Col1 Datatype NOT NULL ,

Col2 Datatype

CONSTRAINT <PrimaryKeyID> PRIMARY KEY(<Col_Name>)

)

Example

CREATE TABLE tbl_CheckPrimaryKey

(

EmpID INT NOT NULL,

EmpName VARCHAR(50)

CONSTRAINT pk_ID PRIMARY KEY (EmpID)

)

Here <Primary Key ID> is not mandatory. But, if you not define the ID, DB engine creates the primary key with its own ID.

Without Primary Key ID you can define the Primary key as below.

Syntax 1

CREATE TABLE <Tbl_Name>                                                       

(

Col1 Data type NOT NULL PRIMARY KEY,              

Col2 Data type

)

Example

CREATE TABLE tbl_Employees

(

EmpID INT NOT NULL PRIMARY KEY,

EmpName VARCHAR(50)

)

Syntax 2

CREATE TABLE <Tbl_Name>                                                       

(

Col1 Datatype NOT NULL PRIMARY KEY,

Col2 Datatype

PRIMARY KEY (<Col_Name>)

)

Example

CREATE TABLE tbl_Employees

(

EmpID INT NOT NULL,

 EmpName VARCHAR(50)

 PRIMARY KEY (EmpID)

)

To find the primary Key ID

Database --> Tables --(Expand by clicking on + symbol which is visible in left side of the table)--> Keys (See the pics below)

EID	Ename	EDept
1	E1	D1
2	E2	D2
3	E3	D3
2	E4	D4

As EID is a Primary Key,…..You cant enter value 2 again in EID column.

If you want to apply primary key to a table which is already created and not contain a Primary key, using alter command you can achieve that.

Syntax

ALTER TABLE <table_name> ADD CONSTRAINT <PrimaryKeyID> PRIMARY KEY (Column_Name)

Example

ALTER TABLE tbl_CheckPrimaryKey ADD CONSTRAINT pk_ID PRIMARY KEY (PKID)

Syntax

ALTER TABLE <table_name>  PRIMARY KEY(Column_Name)

Example

ALTER TABLE tbl_CheckPrimaryKey ADD  PRIMARY KEY(PKID)

To apply Primary to a set of columns

We can apply primary key to set of columns in a table. 

Syntax1

CREATE TABLE <Tbl_Name>

 (

Col1 Datatype NOT NULL PRIMARY KEY,

Col2 Datatype,

Col3 Datatype

PRIMARY KEY (<Col_Name1,Col_Name2,Col_Name3...>)

)

Example

CREATE TABLE tbl_Employees

(

EmpID INT NOT NULL,

EmpName VARCHAR(50),

EmpDept VARCHAR(50)

PRIMARY KEY (EmpID,EmpName)

)

Syntax2

CREATE TABLE <Tbl_Name>

(             

Col1 Datatype NOT NULL PRIMARY KEY,

Col2 Datatype,

Col3 Datatype

Constraint <primarykey_ID> PRIMARY KEY  (<Col_Name1,Col_Name2,Col_Name etc...>)            

)

Example

CREATE TABLE tbl_Employees

(

EmpID INT NOT NULL,

EmpName VARCHAR(50),

EmpDept VARCHAR(50)

Constraint <P_ID> PRIMARY KEY (EmpID,EmpName)

)

EID	EName	EDept
0	E0	D0
1	E1	D1
2	E2	D2
3	E3	D3
3	E4	D4
3	E3	D5

Wrong,… as already data exists there with the same combination of EID, ENAME.

By right Clicking on the table, you can go to Design of the table. There you can find the Primary key column which is visible with Key symbol.

Interview questions:

1	What is a Constraint?
2	What is a primary Key?
3	Can you enter null value in a primary Key?
4	Write the Primary key syntax
5	Can you define primary Key to more than one Column?
6	If you feel yes, write the syntax.
7	Can you add Primary Key to a table, which is already, exists? If yes write the syntax
7	Can you remove Primary key? If yes write the syntax