UNIQUE constraint

A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.

Overview

A UNIQUE constraint prevents duplicate values from being inserted into a column or set of columns. It's commonly used for things like email addresses, usernames, or external identifiers where duplicates would indicate a data error, even though the column isn't the table's primary key.

UNIQUE vs. PRIMARY KEY

A table can have only one PRIMARY KEY, and primary key columns implicitly disallow NULL. A table can have any number of UNIQUE constraints, and unlike primary keys, most databases (following the SQL standard) treat NULL as unequal to any other value — including another NULL — so multiple NULLs are typically allowed in a UNIQUE column.

Composite Uniqueness

UNIQUE can also apply across a combination of columns, so that the combination must be distinct even if individual columns repeat:

Copy code
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    UNIQUE (student_id, course_id)
);

DuckDB Support

DuckDB supports UNIQUE constraints on a single column or a combination of columns:

Copy code
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email VARCHAR UNIQUE
);

Under the hood, DuckDB automatically builds an Adaptive Radix Tree (ART) index for any column with a PRIMARY KEY or UNIQUE constraint, which is also what makes point lookups on that column fast in addition to enforcing uniqueness.

Related terms

primary key →

A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.

Composite key →

A composite key is a primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique on its own.

CHECK constraint →

A CHECK constraint is a rule attached to a table column (or the table as a whole) that requires every row to satisfy a given boolean expression.

DISTINCT →

DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.

Database index →

A database index is a data structure that stores a subset of a table's data in an order optimized for fast lookups, letting queries find matching rows without scanning the whole table.

Data profiling →

Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.

FAQS

Following standard SQL semantics, a UNIQUE constraint permits multiple NULL values, since NULL is never considered equal to another NULL.

Yes, in DuckDB a UNIQUE (or PRIMARY KEY) constraint automatically creates an Adaptive Radix Tree (ART) index on the constrained column(s).