SQL

Difference between Primary Key and Unique Key in SQL

Primary Key
The PRIMARY KEY Constraint Uniquely identifies each record in a database table. Primary Keys must contain UNIQUE values A primary key column can not contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.

Primary Key following the Example:

Create Table EmployeeTbl
(
     EmpID INT NOT NULL,
     Name VARCHAR(50),
     Address VARCHAR(100),
     PRIMARY KEY(EmpID)
);

The PRIMARY KEY is defined as Auto Increment that makes ID column as Identity – Unique Columns, start values from 1, should have an increment of 2 like 1,2,3,4,5,…so on.

Unique Key

Unique Key Constraint to enforce Uniqueness of a column. Unique Key allows one Null value.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

Difference between Primary Key and Unique Key

Primary Key	Unique Key
The primary key constraint unique identifier for each record in the table.	The unique key constraint unique identifier for records when the primary key is not present in the table.
Null values do not allow the Primary keys.	The unique key allows only one null value.
It enforces entity integrity.	It enforces unique data.
By Default, Primary Key creates a Clustered Index on the table.	By Default, Unique Key creates a Non-Clustered Index on the table.
A table can have only 1 primary key.	A table can have multiple unique keys.
We can’t change or delete the primary key values.	We can update the unique key column values.
The primary key optimazation is slow as compared to unique key.	The unique key optimazation is fast as compare to primary key.
syntax of primary key: CREATE TABLE Employee ( Id INT PRIMARY KEY, name VARCHAR(150), address VARCHAR(250) )	syntax of unique key: CREATE TABLE Person ( Id INT UNIQUE, name VARCHAR(150), address VARCHAR(250) )