SQL UNIQUE

What is the UNIQUE constraint in SQL?

UNIQUE enforces that all non-null values in a column or set of columns are different across the entire table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNIQUE:

SQL UNIQUE Full Explanation

UNIQUE is a table-constraint keyword that guarantees no two rows share the same non-null value (or combination of values) in the constrained column list. It can be declared inline on a single column or as a separate constraint on one or multiple columns. Unlike PRIMARY KEY, a table may contain multiple UNIQUE constraints and they allow multiple NULLs in most relational databases (because NULL is considered unknown, not equal). When a DML statement attempts to insert or update data that violates a UNIQUE rule, the operation fails with an error. Vendors usually back UNIQUE constraints with a unique index, so look-ups and joins that filter on the constrained columns are often faster. Some dialects (e.g., Oracle) also accept UNIQUE in SELECT to mean DISTINCT, but this document focuses on the constraint form. Caveats: behaviour with NULLs varies subtly by dialect; adding a UNIQUE constraint on large existing tables can be slow or blocked by duplicates; dropping the constraint removes the uniqueness guarantee.

SQL UNIQUE Syntax

-- Column-level constraint
CREATE TABLE table_name (
    column_name data_type UNIQUE
);

-- Table-level, multi-column constraint
CREATE TABLE table_name (
    col1 data_type,
    col2 data_type,
    UNIQUE (col1, col2)
);

-- Add to existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (col1, col2);

SQL UNIQUE Parameters

Example Queries Using SQL UNIQUE

-- 1. Single-column UNIQUE
CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,
    email       VARCHAR(255) UNIQUE,
    full_name   TEXT
);

-- 2. Multi-column UNIQUE
CREATE TABLE enrollments (
    student_id  INT,
    course_id   INT,
    grade       CHAR(2),
    UNIQUE (student_id, course_id)
);

-- 3. Attempting duplicate insert triggers error
INSERT INTO users (email, full_name)
VALUES ('ada@example.com', 'Ada Lovelace');

INSERT INTO users (email, full_name)
VALUES ('ada@example.com', 'Ada L.');  -- fails

Expected Output Using SQL UNIQUE

Tables are created with unique indexes
The second INSERT raises a duplicate-key error and is rolled back (or the statement fails) because the email already exists

Use Cases with SQL UNIQUE

Enforce business rules such as one account per email address
Guarantee composite keys like (student_id, course_id) are unique for enrolment tables
Speed up look-ups through automatically created unique indexes
Prevent data drift when multiple applications write to the same table

Common Mistakes with SQL UNIQUE

Thinking UNIQUE behaves exactly like PRIMARY KEY; UNIQUE allows multiple NULLs
Adding a UNIQUE constraint without first cleaning existing duplicate data
Forgetting to index additional columns referenced with a partial UNIQUE in some dialects
Assuming all databases treat NULLs the same; SQL Server allows only one NULL per UNIQUE column when ANSI_NULLS is OFF

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between UNIQUE and PRIMARY KEY?

PRIMARY KEY combines UNIQUE and NOT NULL in one constraint and can appear only once per table. UNIQUE permits multiple NULLs and you may declare several per table.

How many NULLs are allowed under a UNIQUE constraint?

Most databases permit any number of NULLs because NULL is not equal to NULL. SQL Server in certain modes restricts it to a single NULL.

Can I drop a UNIQUE constraint?

Yes. Use ALTER TABLE table_name DROP CONSTRAINT constraint_name (or DROP INDEX in MySQL). After dropping it, the table no longer validates new rows for uniqueness.

Does UNIQUE automatically create an index?

In almost every major database it does. The engine builds a unique index to enforce the rule and speed up look-ups, but the implementation details vary.