PRIMARY KEY CONSTRAINT

            Primary key constraint doesn’t allow duplicate and null values insertion. We can create only one primary key on a table.

Applying Primary Key constraint on a new table:

Syntax:-

CREATE TABLE <Table name>

(<Column 1> <Data type>,

<Column 2> <Data type>,

.

.

.

<Column N> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (Column 1, Column 2,...,Column N)

)

Example 1: Applying constraint on single column with constraint name.

CREATE TABLE ConstraintTBL_PK1

(

ID INT

,Name VARCHAR(10)

,CONSTRAINT ID_PK1 PRIMARY KEY(ID)

)

Output: SP_HELP ConstraintTBL_PK1

Example 2: Applying constraint on single column with default constraint name.

CREATE TABLE ConstraintTBL_PK2

(

ID INT

,Name VARCHAR(10)

,PRIMARY KEY (ID)

)

Note: If we are not mentioned constraint name, it will set some default name as constraint name.

Output: SP_HELP ConstraintTBL_PK2

Example 3: Applying constraint on multiple columns.

CREATE TABLE ConstraintTBL_PK3

(

ID INT

,Name VARCHAR(10)

,CONSTRAINT ID_PK3 UNIQUE(ID,Name)

)

Output: SP_HELP ConstraintTBL_PK3

Apply Primary key constraint on existing table:

• The specific column(s) should not have any duplicate or null values then only we can apply Primary Key constraint.

• We cannot define primary key constraint on nullable columns. First we should define not null constraint.

Syntax: 

 ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Column 1>, <Column 2>,…,<Column N>)

Example:

Table creation:

CREATE TABLE ConstraintTBL_PK4

(

ID INT

,Name VARCHAR(10)

)

Not null constraint:

ALTER TABLE ConstraintTBL_PK4 ALTER COLUMN id INT NOT NULL;

Primary key constraint:

ALTER TABLE ConstraintTBL_PK4 ADD CONSTRAINT ID_PK4 PRIMARY KEY(ID);

Output: SP_HELP ConstraintTBL_PK4

Drop constraint:

Syntax:

ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name>

Example:

ALTER TABLE ConstraintTBL_PK4 DROP CONSTRAINT ID_PK4

Output: