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