UNIQUE Constraints in Practice

A UNIQUE constraint guarantees that no two rows in the table hold the same value in the constrained column or column combination. That is the whole of it, and the simplicity is what makes it valuable: the rule is enforced by the engine on every insert and update, no matter which code path is writing.

What it does not guarantee is worth being precise about. It does not guarantee that the value identifies the row — that is the primary key's job, and a UNIQUE column is often nullable, so it may not identify anything. It does not guarantee case-insensitive uniqueness unless the column's collation says so: in SQLite and PostgreSQL, '[email protected]' and '[email protected]' are two different values under a UNIQUE constraint, so two accounts can be created that differ only in case. It does not guarantee anything across tables, and it does not stop a duplicate if the value is NULL — because SQL treats each NULL as distinct from every other NULL.

That NULL behaviour is the detail that catches people out, and it flows directly from three-valued logic. A unique constraint asks whether two values are known to be equal. Two unknowns are not known to be equal, so the uniqueness rule never fires for NULLs. The practical effect is that a table with a nullable unique email column can hold any number of rows with a NULL email, which is usually what you want — an unknown email is not a duplicate of another unknown email.

One more thing it does not do: enforce a rule across a join. If two tables each have a UNIQUE column, nothing stops the same value appearing in both. If that matters, it needs a foreign key or a shared lookup table.

Put the UNIQUE on the business identifier, and use INSERT OR IGNORE when a duplicate should be skipped rather than fatal.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL UNIQUE,
  display_name VARCHAR(120) NOT NULL
);

INSERT INTO accounts (id, email, display_name)
VALUES (1, '[email protected]', 'Ada Lovelace');

-- A second account with the same email would raise:
--   UNIQUE constraint failed: accounts.email
-- INSERT OR IGNORE makes that a no-op instead of an error.
INSERT OR IGNORE INTO accounts (id, email, display_name)
VALUES (2, '[email protected]', 'Ada L.');

SELECT id, email, display_name FROM accounts;

One row comes back: id 1 with [email protected]. The duplicate was ignored, and note that the whole attempted row was discarded — the display name 'Ada L.' was not written either, because the constraint violation rejected the entire insert rather than just the offending column.

That behaviour is why INSERT OR IGNORE is the right tool for idempotent ingestion and the wrong tool for anything else. It also suppresses NOT NULL violations and CHECK constraint failures, not just uniqueness problems, so a genuinely invalid row can vanish without a trace. If you need to know what was skipped, count the rows you sent and the rows that landed and compare them.

The alternative is to let the error happen and handle it deliberately. For a sign-up flow, that means catching the constraint violation and returning "that email is already registered" to the caller — which is a better user experience than silently doing nothing, and much better than creating a second account. That is the case for a UNIQUE constraint on the email column: it turns a race condition between two concurrent sign-ups into a clean, handled conflict rather than duplicate data.

One design note worth raising: make the email column NOT NULL as well as UNIQUE, so a NULL email cannot be used to bypass the uniqueness check, and consider whether the uniqueness should be case-insensitive. If it should, use a case-insensitive collation on the column in MySQL, a citext or a functional unique index on lower(email) in PostgreSQL, and a functional index in SQLite.

Use a partial unique index, which adds the uniqueness rule only for the rows that match a WHERE condition. That is how you express "this must be unique, but only where the value is actually present".

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200),
  country VARCHAR(60)
);

-- Only rows that actually have an email are constrained.
CREATE UNIQUE INDEX uq_customers_email
  ON customers (email)
  WHERE email IS NOT NULL;

INSERT INTO customers (id, email, country) VALUES
  (1, '[email protected]',  'UK'),
  (2, '[email protected]','US'),
  (3, NULL,              'UK'),
  (4, NULL,              'UK');

SELECT id, email, country FROM customers ORDER BY id;

Four rows come back. The two rows with a NULL email are both accepted despite the unique index, because the index's WHERE clause excludes them. A third row with [email protected] would be rejected, because that row is in the index's scope.

Partial indexes are supported by SQLite and PostgreSQL, and by MySQL 8 does not support them — in MySQL the equivalent is a generated column with an explicit value for the rows that are out of scope, plus a unique index on that column. That generated-column trick is the portable workaround when you need the same rule across engines.

There is a related variant worth knowing. If the rule should be case-insensitive, index the expression rather than the column: a unique index on lower(email) in SQLite and PostgreSQL treats '[email protected]' and '[email protected]' as the same value, which is usually what a user expects from an email uniqueness rule. The trade-off is that the index no longer matches the raw column, so a lookup that does not use the same expression will not benefit from it — every query against that column has to use lower(email) consistently.

And if the rule spans several columns — one device per customer per product, say — a composite unique index over those columns is the answer, with the same caveat about NULLs: any row with a NULL in one of the constrained columns escapes the constraint entirely, because SQL cannot say whether it duplicates anything.