Unique Key in RDBMS

A Unique Key is a constraint in RDBMS that ensures all values in a column (or set of columns) are distinct. Many users confuse Primary Key with Unique Key since both enforce uniqueness, but they differ in NULL handling, volume, and modifiability.

The unique key constraint is essential for maintaining data integrity by preventing duplicate entries while providing more flexibility than primary keys. It's commonly used for columns like email addresses, phone numbers, or social security numbers where uniqueness is required but the field may not serve as the primary identifier.

Primary Key vs Unique Key

Visual Representation

The following diagram illustrates the concept of unique key constraints in a database table ?

Example

In the following Employee table, EmpID is the primary key and EmpEmail has a unique key constraint ?

EmpID cannot be NULL or duplicated (primary key). EmpEmail cannot be duplicated but allows one NULL value (unique key).

SQL Example

The following SQL creates a table with both primary key and unique key constraints ?

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    EmpEmail VARCHAR(100) UNIQUE
);

-- Valid: unique email
INSERT INTO Employee VALUES (1, 'Alice', 'alice@company.com');

-- Valid: NULL is allowed for unique key
INSERT INTO Employee VALUES (2, 'Bob', NULL);

-- Invalid: duplicate email (violates unique constraint)
-- INSERT INTO Employee VALUES (3, 'Charlie', 'alice@company.com');

-- Display the table
SELECT * FROM Employee;

The output of the above code is ?

EmpID | EmpName | EmpEmail
------|---------|------------------
1     | Alice   | alice@company.com
2     | Bob     | NULL

Conclusion

A Unique Key prevents duplicate values in a column while allowing one NULL. Unlike a Primary Key (which is limited to one per table and cannot be NULL), a table can have multiple Unique Key constraints to enforce uniqueness on different columns.