SQL Unique Constraint

The UNIQUE constraint ensures that all values in a column or set of columns are distinct across all rows in a table. It prevents duplicate entries, enforcing entity uniqueness for fields that are not the primary key (such as Social Security Numbers, phone numbers, or email addresses).

Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints.

Basic Syntax & Examples

UNIQUE constraints can be defined at the column level (for single columns) or at the table level (for single or composite columns).

1. Column-Level Unique Constraint

CREATE TABLE Users (
    UserID    INT PRIMARY KEY,
    Username  VARCHAR(50) NOT NULL UNIQUE,
    Email     VARCHAR(100) UNIQUE,
    Phone     VARCHAR(20)
);

In this table:

• UserID is the primary key.
• Username and Email must contain unique values across all records.

2. Table-Level Unique Constraint (Composite Unique Key)

When a combination of multiple columns must be unique together, declare a table-level UNIQUE constraint:

CREATE TABLE EventRegistrations (
    RegistrationID INT PRIMARY KEY,
    EventID        INT NOT NULL,
    AttendeeID     INT NOT NULL,
    RegistrationDate DATE,
    
    -- Prevents an attendee from registering for the same event twice
    CONSTRAINT UQ_Event_Attendee UNIQUE (EventID, AttendeeID)
);

In this design, individual EventID or AttendeeID values can repeat, but the combination of (EventID, AttendeeID) must be unique.

Behavior with NULL Values

One key functional distinction between UNIQUE and PRIMARY KEY constraints is how database engines handle NULL values:

• PostgreSQL, MySQL, SQLite, Oracle: Allow multiple NULL values in a column with a UNIQUE constraint. Under standard ANSI SQL rules, NULL represents an "unknown" value, so two NULLs are not considered equal to each other.
• SQL Server (T-SQL): Permits only one NULL value by default in a standard UNIQUE column. Inserting a second NULL violates the constraint unless a filtered unique index is used (WHERE Column IS NOT NULL).

Adding a UNIQUE Constraint to an Existing Table

You can enforce uniqueness on existing columns using ALTER TABLE:

ALTER TABLE Users
ADD CONSTRAINT UQ_Users_Phone UNIQUE (Phone);

Note: The ALTER TABLE statement will fail if the target column already contains duplicate values. Duplicates must be resolved or removed before attaching the constraint.

Dropping a UNIQUE Constraint

To remove a UNIQUE constraint rule from a table:

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE Users
DROP CONSTRAINT UQ_Users_Phone;

-- MySQL / MariaDB (MySQL treats UNIQUE constraints as indexes)
ALTER TABLE Users
DROP INDEX UQ_Users_Phone;

UNIQUE Constraint vs. PRIMARY KEY