# SQL UNIQUE Constraint: Prevent Duplicate Values

- URL: https://betteranalyst.com/sql/unique

SQL guide · Constraints

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

- [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key)
- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL CREATE UNIQUE INDEX](https://betteranalyst.com/sql/create-unique-index)
- [SQL ADD CONSTRAINT](https://betteranalyst.com/sql/add-constraint)
- [SQL DROP CONSTRAINT](https://betteranalyst.com/sql/drop-constraint)
- [SQL CHECK](https://betteranalyst.com/sql/check)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)