Primary Key Constraint In SQL Server

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 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 the 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.

Conclusion

Today we learned how to Create, Alter and Drop  Primary Key  constraint in SQL Server with multiple examples.
I hope you enjoyed this post and found it useful.