Primary Key in SQL

Published : 21 December 2024
Updated : 01 January 2025

207 Views

A Primary Key is a database constraint that ensures uniqueness records in the table. It guarantees no two rows can have the same value in the primary key column(s). The Primary Key not only ensures uniqueness but also improves data access speed and is used to establish relationships between tables.

Both Primary Key and Unique Key ensure uniqueness in a table. The main differences are a Primary Key can not allow NULL value and only one primary key is allowed per table, while a Unique Key allows NULL values but requires each non-null value to be unique and multiple unique keys can exist in a table.

Syntax

Creating a Primary Key at the Time of Table Creation

CREATE TABLE TableName(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....................
   PRIMARY KEY(column(s))
);

CREATE TABLE TableName(
   column1 datatype PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   .....................
);

Creating a Primary Key On an Existing Table

ALTER TABLE TableName ADD CONSTRAINT PRIMARY KEY (column(s));

Dropping Primary Key

ALTER TABLE TableName DROP PRIMARY KEY;

Example

In the following example, we are trying to create a table with name Employees in an SQL database. While creating the table, we will add the constraint "PRIMARY KEY" on the column EmployeeId.

CREATE TABLE `Employees` (
    `EmployeeId` INT PRIMARY KEY ,
    `Name` VARCHAR(50) NOT NULL,
    `Mobile` VARCHAR(20) NOT NULL,
    `JobTitle` VARCHAR(100) NOT NULL,
    `Department` VARCHAR(100) NOT NULL,
    `salary` DECIMAL(10, 2)
);

Example (On an Existing Table)

In the following example, We are adding Primary Key on an existing table Employees on column EmployeeId.

ALTER TABLE Employees ADD CONSTRAINT PRIMARY KEY (EmployeeId);

In the following example, We are adding Primary Key on an existing table Employees on column Name and Mobile.

ALTER TABLE Employees ADD CONSTRAINT PRIMARY KEY (Name, Mobile);

Example (Dropping Primary Key)

In the following example, We are dropping Primary Key on table Employees.

ALTER TABLE Employees DROP PRIMARY KEY;

Primary Key vs Unique Key

While both Primary Key and Unique Key constraints ensure uniqueness for the column(s). Below are some key differences :-

Primary Key

A table can only have one primary key.
It cannot accept NULL values
It automatically creates a unique index for efficient querying.

Unique Key

A table can have multiple unique keys.
It can accept NULL values.
It also automatically creates a unique index but it does not enforce the "no NULL" constraint.

Key Points of Primary Key

Primary key is a unique identifier for each record in a table.
Two rows in a table can’t have the same primary key value. In other words, you can say, Every primary key value must be unique within the table.
Primary key establish relationships between tables and ensure the integrity of the data.
Primary key is automatically indexed by the database.
A primary key can not contain NULL values.
When a primary key is created on multiple columns of a table, it is called a Composite Key.