SQL guide · Constraints

SQL UNIQUE Constraint: Prevent Duplicate Values

A UNIQUE constraint guarantees that no two rows have the same value in a column, or the same combination of values across several columns. It is the right tool when a column is not the primary key but still must not repeat, such as an email address, a username, a SKU or a tax number. Attempts to insert or update a row that would create a duplicate fail with an error, so bad data never enters the table.

The difference from a primary key is mainly about count and nulls. A table can have only one primary key but many unique constraints. Primary key columns can never be null, whereas unique columns usually can, though how nulls are treated varies by database. Unique constraints can also be the target of foreign keys, which lets other tables reference something other than the primary key.

Internally, the database enforces uniqueness with a unique index, which is why a unique constraint also speeds up lookups on those columns. Creating a unique index with CREATE UNIQUE INDEX achieves nearly the same enforcement. The constraint form is preferable when you want the rule to be visible as part of the table definition, while the index form is useful when you need options such as filtered or partial indexes.

Basic syntax

CREATE TABLE table_name (
  column1 data_type UNIQUE,
  column2 data_type,
  column3 data_type,
  CONSTRAINT uq_name UNIQUE (column2, column3)
);

ALTER TABLE table_name
ADD CONSTRAINT uq_name UNIQUE (column1);

A column-level UNIQUE applies to that one column. A table-level UNIQUE (a, b) applies to the pair as a whole, so each column can repeat on its own as long as the combination is distinct. uq_name is optional but makes later changes easier.

SQL UNIQUE examples

Unique email on a single column

Each customer must have a different email address. The second insert reuses the same address and is rejected, which stops duplicate accounts from being created by accident.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  email       VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO customers VALUES (1, 'Ada Lovelace', 'ada@example.com');

-- Fails: duplicate email
INSERT INTO customers VALUES (2, 'Ada L.', 'ada@example.com');

Unique combination of columns

A product code only needs to be unique within a supplier. The composite constraint allows the code A100 for two different suppliers but blocks a second A100 for the same supplier.

CREATE TABLE supplier_products (
  id          INT PRIMARY KEY,
  supplier_id INT NOT NULL,
  product_code VARCHAR(30) NOT NULL,
  CONSTRAINT uq_supplier_code UNIQUE (supplier_id, product_code)
);

Add a unique constraint to an existing table

Use ALTER TABLE once you have confirmed that no duplicates exist. The first query lists any email that appears more than once, so you can resolve those rows before adding the rule.

SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

Unique ignoring soft-deleted rows

Sometimes uniqueness should only apply to active rows. PostgreSQL and SQLite support a partial unique index, which enforces the rule only where the condition is true. This is not available as a plain UNIQUE constraint.

-- PostgreSQL / SQLite
CREATE UNIQUE INDEX uq_active_email
ON customers (email)
WHERE deleted_at IS NULL;

Where SQL dialects differ

PostgreSQL: Multiple NULL values are allowed in a unique column because nulls are considered distinct. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to treat nulls as equal. Comparisons are case sensitive for text unless you use citext or a lower-case expression index.

MySQL: Allows multiple nulls in a unique index. Text comparisons follow the column collation, and most default collations are case insensitive, so Ada@x.com and ada@x.com count as duplicates. Drop with ALTER TABLE t DROP INDEX uq_name.

SQL Server: A unique constraint permits only one NULL per column, because nulls are treated as equal for uniqueness. To allow many nulls, create a filtered unique index such as CREATE UNIQUE INDEX ... WHERE col IS NOT NULL.

SQLite: Allows multiple nulls in unique columns. Constraints must be declared in CREATE TABLE; to add one later, use CREATE UNIQUE INDEX, which enforces the same rule.

Oracle: Allows multiple rows where all unique columns are null, but for composite constraints a row with some nulls and some values is still checked against the non-null parts. Enforcement is done with an automatically created unique index.

Common mistakes

  • Assuming null counts as a duplicate. In most engines you can store many nulls in a unique column, but SQL Server allows only one.
  • Ignoring case and whitespace. Ada@Example.com and ada@example.com may or may not clash depending on collation, so normalise values before storing them.
  • Expecting a composite constraint to make each column unique. UNIQUE (a, b) only prevents repeated pairs.
  • Adding the constraint without checking for existing duplicates first, which makes the ALTER TABLE fail on a large table and can hold locks while it tries.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator