SQL Primary Key Constraint

SQL Primary Key is a single column or a group of column which uniquely identify each record of table. Primary Key Column is always Not NULL column and it does not accept NULL value.

By Default, SQL creates a clustered index on primary key, which defines the order of record.

Syntax: Create Primary Key on Create Table

The below SQL will create a primary key on column StatusID on OrderStatus Table

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50)    

)

It creates primary key on StatusID and by default it creates clustered Index ‘PK__OrderSta__C8EE20434A6E8ADF’ on StatusID, as you see below

There is another way to create primary key constraint on table and also you can give primary key name ‘OrderStatus_PK’

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50),

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED ( StatusID ASC), 

)

Syntax: Create Primary Key on Alter Table

By using Alter SQL query, Primary key constraint can be created on existing table’s column

ALTER TABLE OrderStatus

ADD PRIMARY KEY (StatusID);

Syntax: Drop Primary Key from Table

By using below SQL, we can drop the primary key constraint from OrderStatus Table.

ALTER TABLE OrderStatus

DROP CONSTRAINT [OrderStatus_PK]