Unique Key in SQL
A Unique Key in SQL is a constraint that ensures all values in a column (or a group of columns) are unique across the table — no two rows can have the same value(s) in those columns.
Key Points about Unique Key:
Guarantees uniqueness of the column(s).
Allows one NULL value per column (behavior may vary by database).
A table can have multiple unique keys, unlike primary keys which are usually one per table.
Helps maintain data integrity.
Automatically creates a unique index to enforce uniqueness.
Syntax
When creating a table:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Email VARCHAR(100) UNIQUE, Username VARCHAR(50), CONSTRAINT unique_username UNIQUE (Username));Adding unique key to an existing table:
ALTER TABLE EmployeesADD CONSTRAINT unique_email UNIQUE (Email);Example
-- Create table with unique key on Email columnCREATE TABLE Users ( UserID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE);-- Insert exampleINSERT INTO Users (UserID, Email) VALUES (1, 'alice@example.com');INSERT INTO Users (UserID, Email) VALUES (2, 'bob@example.com');-- This next insert will fail if Email already exists:INSERT INTO Users (UserID, Email) VALUES (3, 'alice@example.com'); -- Error!Difference between Primary Key and Unique Key
| Feature | Primary Key | Unique Key |
|---|---|---|
| Allows NULLs | No (usually) | Yes (typically one NULL allowed) |
| Number per table | One | Multiple allowed |
| Enforces uniqueness | Yes | Yes |
| Used for identifying rows | Yes | No |
If you want examples or more details about unique keys in a specific SQL dialect, just ask!
