MySQL UNIQUE Constraint: Prevent Duplicates

A unique constraint says that no two rows may have the same value in a column or a combination of columns. MySQL implements it with a unique index. UNIQUE KEY, UNIQUE INDEX and the UNIQUE constraint are the same thing.

query.sql

SQL

CREATE TABLE accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    username VARCHAR(30) NOT NULL,
    CONSTRAINT uq_accounts_email UNIQUE (email),
    UNIQUE KEY uq_accounts_username (username)
);
INSERT INTO accounts (email, username) VALUES ('asha@example.com', 'asha');
INSERT INTO accounts (email, username) VALUES ('asha@example.com', 'asha2');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'accounts.uq_accounts_email'

The error message names the key accounts.uq_accounts_email. Good names make errors readable.

The race-safe way to enforce uniqueness

Do not check uniqueness only in code:

Text

PLAIN

1
2

1. SELECT ... WHERE email = ?     -- not found
2. INSERT ...                      -- but another request inserted it between 1 and 2

Two requests running at the same moment can both pass step 1. A UNIQUE constraint is checked atomically by the database, so exactly one insert wins and the other gets error 1062 (duplicate entry). The application catches that error and shows a friendly message.

Composite unique keys

Uniqueness of a combination:

query.sql

SQL

CREATE TABLE room_bookings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room VARCHAR(5) NOT NULL,
    booked_on DATE NOT NULL,
    guest VARCHAR(20) NOT NULL,
    UNIQUE KEY uq_room_day (room, booked_on)
);
INSERT INTO room_bookings (room, booked_on, guest) VALUES ('101', '2026-08-01', 'Asha'), ('101', '2026-08-02', 'Ben'), ('102', '2026-08-01', 'Chitra');
INSERT INTO room_bookings (room, booked_on, guest) VALUES ('101', '2026-08-01', 'Dev');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '101-2026-08-01' for key 'room_bookings.uq_room_day'

Room 101 can be booked on different days, and different rooms on the same day, but the same room on the same day only once.

NULL and unique

In MySQL a unique index allows multiple NULL values, because NULL is “unknown” and two unknowns are not equal:

query.sql

SQL

1
2
3

CREATE TABLE emails (id INT AUTO_INCREMENT PRIMARY KEY, backup_email VARCHAR(50) NULL, UNIQUE KEY uq_backup (backup_email));
INSERT INTO emails (backup_email) VALUES (NULL), (NULL), ('a@example.com');
SELECT COUNT(*) AS rows_inserted FROM emails;

Output

OUTPUT

+---------------+
| rows_inserted |
+---------------+
|             3 |
+---------------+
1 row in set

Three rows, two with NULL. This is useful for optional unique values (an optional username). It is a trap for composite keys: UNIQUE (a, b) allows (1, NULL) twice. If NULL in a unique column should count as a duplicate, make the column NOT NULL.

Case and trailing spaces

The collation decides what “the same value” means. With the default case-insensitive collation, Asha@Example.com duplicates asha@example.com:

query.sql

SQL

INSERT INTO accounts (email, username) VALUES ('ASHA@EXAMPLE.COM', 'someone');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'ASHA@EXAMPLE.COM' for key 'accounts.uq_accounts_email'

That is usually what you want for emails and usernames (Case-Sensitive Searches). Also store emails trimmed and lowercase, to keep the data tidy.

Unique prefixes of long text

A unique index on a long VARCHAR or TEXT needs a prefix length, and then uniqueness applies to the prefix only:

query.sql

SQL

1
2
3

CREATE TABLE urls (id INT AUTO_INCREMENT PRIMARY KEY, url TEXT NOT NULL, url_hash CHAR(64) AS (SHA2(url, 256)) STORED, UNIQUE KEY uq_url_hash (url_hash));
INSERT INTO urls (url) VALUES ('https://example.com/a'), ('https://example.com/b');
INSERT INTO urls (url) VALUES ('https://example.com/a');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '2dce0a4c50441bfccfa9caf4b58c3cba6e06c420505dd829f0436de1aa44baac' for key 'urls.uq_url_hash'

For long values, store a hash in a generated column and put the unique index on that.

Add a unique constraint to existing data

It fails if duplicates exist. Find them first:

query.sql

SQL

CREATE TABLE subscribers (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(40));
INSERT INTO subscribers (email) VALUES ('a@example.com'), ('b@example.com'), ('a@example.com');

SELECT email, COUNT(*) AS copies FROM subscribers GROUP BY email HAVING COUNT(*) > 1;
ALTER TABLE subscribers ADD CONSTRAINT uq_subscribers_email UNIQUE (email);

Output

OUTPUT

+---------------+--------+
| email         | copies |
+---------------+--------+
| a@example.com |      2 |
+---------------+--------+
1 row in set

ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'subscribers.uq_subscribers_email'

Clean up the duplicates (DELETE with JOIN), and run the ALTER again.

UNIQUE vs PRIMARY KEY

	PRIMARY KEY	UNIQUE
Per table	One	Many
`NULL` allowed	No	Yes (multiple)
Used as the row identifier / clustered index	Yes	No
Referenced by foreign keys	Yes	Yes (as a candidate key)

Unique index or application check?

Always have the unique index. You may also check in the application first, to give the user a better message before the insert. The index is the guarantee, and the application check is a convenience.

lightbulb

Handling the error

In code, catch the duplicate-key error (number 1062, SQLSTATE 23000) and turn it into “This email is already registered”. Do not show the raw database message to users.