Primary Key Constraint In SQL Server

Last updated on December 7th, 2022 at 11:32 pm

The PRIMARY KEY constraint in SQL Server is a field or column which ensures that each entry in a table is uniquely recognized.
Primary keys must have UNIQUE values and cannot have NULL values.
A table can only have one primary key, which can be made up of one or more columns (fields).

A primary key can be created at the column or table level. The following is an example to illustrate this:

Table of Contents

What is the primary key in SQL Server?

In short, We use to create a primary key column to uniquely identify records and access data quickly and effectively from the database.

When you create a primary key on a table column, the database engine automatically creates a unique index on that column.
A primary key column doesn’t allow inserting null or duplicate values.
A Primary key can consist of up to 16 columns.
A table can have only one primary key which can be applied to single or multiple columns.
The primary key which consists of multiple columns is known as the Composite primary key.
A Primary Key constraint can be applied to the columns having any data types.
A primary key by default creates a clustered index on the column.

Syntax to create a primary key

The following is the syntax to create a primary key in the SQL server.

-- Column level Primary key

  CREATE TABLE TableName
  (
  Column1 data_type [NOT NULL ] [ PRIMARY KEY ],
  Column2 data_type [ NULL | NOT NULL ],
  Column3  ...
  );

 -------------------------------

-- Table level Primary key

CREATE TABLE TableName
( 
  Column1 data_type [ NULL | NOT NULL ],
  Column2 datatype [ NULL | NOT NULL ],
  Column3  ...
  CONSTRAINT ConstraintName PRIMARY KEY (Column1, Column2)
);

Create Primary Key Constraint Using Create Table statement

The following SQL query will create a Primary Key on the ID column when the Employee table is created.

-- Column level Primary key
CREATE TABLE Employee 
 (
   ID INT CONSTRAINT PK_ID PRIMARY KEY,

   NAME VARCHAR (50),

   EMAIL VARCHAR(60)

  )
-- OR

-- Table level Primary key
 CREATE TABLE Employee
  (
   ID INT NOT NULL,

   NAME VARCHAR (50),

   EMAIL VARCHAR(60)

   CONSTRAINT PK_ID PRIMARY KEY(ID)

  )

Let’s execute the above SQL query where we have created a primary key on the ID column of the employee table.

Let’s execute the following SQL insert statement to add a new record to the Employee table.

-- Insert a new record into the Employee table.

INSERT INTO EMPLOYEE VALUES(1,'Shekh Ali','shekh123@gmail.com');

Add duplicate value in the primary key column

Primary key columns do not accept duplicate values. Attempting to insert the same value that is already in the primary key column will result in an error.
Let’s execute the following SQL INSERT statement with duplicate values.

Primary key Constraint duplicate record 1

You can see in the above example that the primary key doesn’t allow to insert the duplicate records in the table.

Insert NULL value in the primary key column

The primary key column doesn’t accept the NULL value. If we try to insert the null value in the primary key column, It will throw an error.

-- Insert NULL value into the primary key column. 
INSERT INTO EMPLOYEE (ID) VALUES (NULL);

Drop primary key constraint in SQL server

Let’s drop a primary key constraint in the SQL server using the ALTER table statement.

-- Drop primary key constraint
 
ALTER TABLE Employee DROP CONSTRAINT PK_ID ;

Create a composite primary key in the SQL server

The primary key consists of multiple columns or fields and is known as the Composite primary key. In the following example, we will create a composite primary key on multiple columns such as ID and EMAIL.

First, let’s make the EMAIL column non-nullable. Otherwise, an error will occur when creating the composite primary key.

-- Make EMAIL column not nullable.
 
ALTER TABLE Employee ALTER COLUMN EMAIL VARCHAR(60) NOT NULL ;

Now let’s create a composite primary key.

-- Create composite primary key

ALTER TABLE Employee ADD CONSTRAINT EMP_PK PRIMARY KEY (ID, EMAIL);

composite primary key is SQL — The composite primary key in SQL Server

Here in the above image, we can see that the Composite primary key is applied over multiple columns such as ID and EMAIL.

Primary key Constraint Limitation in SQL Server

Only one PRIMARY KEY constraint can exist in a table.
If the primary key is a column, the value must be unique and not NULL. If the primary key is made up of many columns, each combination of values in these columns must be unique.
In SQL Server, a primary key cannot have more than 16 columns and a total key length of more than 900 bytes.
The number of indexes on the table cannot exceed 999 nonclustered indexes and 1 clustered index because of the index created by the primary key constraint.

Reference: Primary Key Constraints

Conclusion

In this Article, we used multiple examples to show how to Create, Alter, and Drop Primary Key Constraints in SQL Server.

I hope you found this post useful. If you have any questions or comments, please leave them below.