UNIQUE

UNIQUE builds an index that rejects duplicates across one column or several, and lets NULL appear as often as it likes.

Syntax[CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] (key_part, ...)

A UNIQUE constraint rejects any row whose key value already exists. In MySQL it is not a separate object from an index: declaring UNIQUE creates a unique index, and the optimizer uses that index for lookups exactly as it would any other.

Shorthand and longhand

UNIQUE, UNIQUE KEY and UNIQUE INDEX are synonyms. The inline column form and the table-level form produce the same index; the difference is only that the longhand lets you name it.

email VARCHAR(255) NOT NULL UNIQUE        -- shorthand, MySQL names the index
CONSTRAINT uq_users_email UNIQUE (email)  -- longhand, same index, your name

Prefer the named form. The generated name is what you must type in a later ALTER TABLE ... DROP INDEX, and it is what appears in the error message your support inbox receives.

Composite keys and NULL

UNIQUE (team_id, user_id) constrains the pair. Either column may repeat on its own; only the combination must be distinct.

The manual is explicit: "A UNIQUE index permits multiple NULL values for columns that can contain NULL." Because NULL is never equal to NULL, a nullable unique column accepts any number of NULL rows. That is usually what you want for an optional token, and a surprise if you read UNIQUE as "at most one row".

What trips people

Distinctness is decided by the column's collation, not by its bytes. MySQL 8.4 defaults to utf8mb4_0900_ai_ci — accent-insensitive and case-insensitive — so 'Jose' and 'josé' collide. A violation raises error 1062, ER_DUP_ENTRY.

Example

Example · sql
CREATE TABLE team_members (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id      BIGINT UNSIGNED NOT NULL,
  user_id      BIGINT UNSIGNED NOT NULL,
  email        VARCHAR(255) NOT NULL,
  invite_token CHAR(32)     NULL,
  PRIMARY KEY (id),

  -- Single column. Equivalent to writing `email VARCHAR(255) NOT NULL UNIQUE`
  -- on the column itself; naming it is the only difference.
  CONSTRAINT uq_team_members_email UNIQUE (email),

  -- Composite: a user appears at most once per team, but may join many teams
  -- and a team may hold many users. Only the pair has to be distinct.
  CONSTRAINT uq_team_members_team_user UNIQUE (team_id, user_id),

  -- invite_token is nullable, so every row that has no token is accepted:
  -- a unique index permits multiple NULLs. Issued tokens stay distinct.
  CONSTRAINT uq_team_members_token UNIQUE (invite_token)
) ENGINE=InnoDB;

When to use it

  • Guarantee one account per email address without a SELECT-then-INSERT race.
  • Stop a user being added to the same team twice with UNIQUE (team_id, user_id).
  • Give INSERT ... ON DUPLICATE KEY UPDATE a key to collide on for daily counters.
  • Enforce distinct invite tokens while leaving the column NULL for members who were never invited.

More examples

Adding UNIQUE to a table that already has rows

Reach for this during a migration that tightens an existing table. The GROUP BY ... HAVING pass tells you the size of the cleanup before you take an outage over it, and ALGORITHM=INPLACE, LOCK=NONE makes MySQL fail loudly if it cannot do the build online rather than silently locking the table.

Example · sql
-- 1. Find what the new constraint would reject. Do this first; the ALTER
--    fails outright on the first duplicate it meets.
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING n > 1;

-- 2. Merge or retire those rows, then build the index. InnoDB creates a
--    secondary index in place, and the table stays readable and writable
--    for the whole build.
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email),
  ALGORITHM=INPLACE, LOCK=NONE;

Controlling what counts as a duplicate

Use the first when duplicate detection is wrong for your data — product SKUs where case is meaningful. Use the second when you want lenient matching in queries but strict uniqueness on a canonical form; a STORED generated column can carry a unique index and cannot drift, because MySQL recomputes it on every write.

Example · sql
-- Under the 8.4 default collation utf8mb4_0900_ai_ci, these three are the
-- same value to a unique index: 'Rene', 'rene', 'René'.

-- Case- and accent-sensitive: 'rene' and 'René' become distinct rows.
ALTER TABLE tags
  MODIFY name VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL;

-- Or keep the insensitive collation for searching and enforce distinctness
-- on a normalised copy the server maintains for you.
ALTER TABLE tags
  ADD COLUMN slug VARCHAR(64)
    GENERATED ALWAYS AS (LOWER(name)) STORED,
  ADD CONSTRAINT uq_tags_slug UNIQUE (slug);

A unique index as the target of an upsert

This is the counter-increment pattern: one statement, no read-modify-write race. It only works because a unique index exists on the identifying columns — without it every call inserts a new row. Keep tables used this way down to a single unique index so the collision is unambiguous.

Example · sql
CREATE TABLE page_views (
  page_id   BIGINT UNSIGNED NOT NULL,
  view_date DATE            NOT NULL,
  views     INT UNSIGNED    NOT NULL DEFAULT 0,
  PRIMARY KEY (page_id, view_date)   -- a PRIMARY KEY is a unique index too
) ENGINE=InnoDB;

-- ON DUPLICATE KEY UPDATE fires when the insert would violate ANY unique
-- index on the table, primary key included. With one unique index the
-- behaviour is obvious; with several, the row that gets updated is the one
-- that collided, which may not be the row you were thinking of.
INSERT INTO page_views (page_id, view_date, views)
VALUES (42, '2026-09-17', 1)
ON DUPLICATE KEY UPDATE views = views + 1;

Related

sql primary keysql create indexsql not nullsql nullsql upsert

Discussion

  • Be the first to comment on this lesson.