SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column or a combination of columns are distinct across all rows in a table. It prevents duplicate entries while allowing NULL values unless otherwise specified.

 

Key Features of SQL UNIQUE Constraint

• Ensures data uniqueness in a column.
• Allows multiple UNIQUE constraints in a table.
• Allows NULL values (except when combined with NOT NULL).
• Can be applied to single or multiple columns (composite unique constraint).

 

SQL UNIQUE Constraint Syntax

Defining UNIQUE in Table Creation

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

• The Email column must contain unique values, preventing duplicate emails.

Adding UNIQUE to Multiple Columns

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    UNIQUE (FirstName, LastName)
);

• Ensures that no two employees have the same FirstName and LastName combination.

 

Adding a UNIQUE Constraint to an Existing Table

ALTER TABLE Employees  
ADD CONSTRAINT UQ_Email UNIQUE (Email);

• Adds a UNIQUE constraint to the Email column.

 

Removing a UNIQUE Constraint

ALTER TABLE Employees  
DROP CONSTRAINT UQ_Email;

 

Inserting Data into a Table with UNIQUE Constraint

INSERT INTO Employees (EmployeeID, Email)  
VALUES (1, '[email protected]');  -- ✅ Successful

INSERT INTO Employees (EmployeeID, Email)  
VALUES (2, '[email protected]');  -- ❌ Error: Duplicate entry for UNIQUE column 

 

Summary

• The UNIQUE constraint ensures that column values are distinct.
• Can be applied to single or multiple columns.
• Allows NULL values, but duplicates are restricted.
• Can be added or removed using ALTER TABLE.