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:
