The Primary Key constraint is used to identify each row uniquely in a database table.

Primary key constraint enforce the following rules.

1. Primary keys must contain UNIQUE values.
2. Primary key column cannot contain NULL values.
3. Most tables should have a primary key, and each table can have only ONE primary key.
4. Primary key can consist of one or more columns on a table (When multiple columns are used as a primary key, they are called a composite key.)
5. Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Create primary key:
There are several ways to create a primary key in a database table.
At the time of table creation:

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar](50) NULL
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([ID] ASC)
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL PRIMARY KEY,
[StudentName] [nvarchar] (50) NULL
)

Method 3:
CREATE TABLE [dbo].[Table1](
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NULL,
PRIMARY KEY ( [ID] )
)

Create primary key on multiple columns:
For defining a PRIMARY KEY constraint on multiple columns and columns should not nullable.
use the following SQL syntax:

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL,
PRIMARY KEY CLUSTERED ( [ID], [StudentName] )
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL
CONSTRAINT [PK_Table1] PRIMARY KEY ( [ID], [StudentName] ASC )
)

Create primary key on existing table:
To create a primary key constraint on the ID columns when Table1 table already exists.

use the following SQL syntax:

ALTER TABLE [Table1]
ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (ID);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column (s) must already have been declared to not contain NULL values (when the table was first created).

Drop primary key constraint:
You can clear the primary key constraints from the table.

use the following SQL syntax:

ALTER TABLE [Table1]
DROP PRIMARY KEY ;

Disable Primary key constraint :
You can disable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
DISABLE;

Enable Primary key constraint:
You can enable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
REBUILD;

Share this: