Primary Key Constraint In SQL Server

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

What is the primary key in SQL Server?

In SQL Server, a  Primary Key  is a field or column which uniquely identifies each and every record in a table.

• • A primary key column doesn’t allow to insert  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 on single or multiple columns.
  • The primary key consist of multiple columns or fields 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.

A primary key can be created at the column or table level.

Syntax to create a primary key

The following are the syntax to create a primary key in 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 into 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

The Primary key column will not accept the duplicate value. It will throw an error If we try to insert the same value which is already there in the primary key column.
Let’s execute the same SQL Insert statement.

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 SQL server

The primary key consist of multiple columns or fields 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.

Let’s first make the EMAIL column not nullable otherwise, we will get an error while 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);

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

Limitations and Restrictions of Primary key Constraint in SQL Server

 A primary key must adhere to the following guidelines:

• • 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, Multiple examples were used to demonstrate how to Create, Alter, and Drop Primary Key Constraints in SQL Server.

I hope you enjoyed this post and found it useful. In case you have any doubt, please post your feedback, question, or comments.

Thanks for visiting.