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]
