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) ) |
