Wednesday, July 24, 2019

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


SQL Primary Key


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]

1 comment:

James Zicrov said...

I think this is a very crucial post related to SQL and REST. Apart from this I also have some interesting topics to share.

SQL Server Load Rest Api

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...