00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Unique Index

Last updated: 4 min read

A unique index is an index that also enforces a rule: no two rows may have the same value (or combination of values) in the indexed columns. It gives you two things at once: fast lookups, and a guarantee against duplicates such as two people booking the same seat.

1. Creating a Unique Index

syntax.sql 
CREATE UNIQUE INDEX index_name ON table_name (col1 [, col2 ...]);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (col1, ...);
A unique index checks the sorted keys before every insertThe unique index on (event_id, seat_no) holds sorted keys. A new booking for event 7 seat B4 is looked up in the index; the key already exists, so the insert is rejected. A booking for seat B5 doesn't exist yet, so it is inserted.uq_event_seat (sorted)event_idseat_no7B27B37B48A1INSERT (7, 'B4')key exists → ERROR 1062INSERT (7, 'B5')not found → inserted
Figure 1: because the index is sorted, checking for a duplicate is a fast lookup.
seats.sql 
CREATE UNIQUE INDEX uq_event_seat ON bookings (event_id, seat_no);
INSERT INTO bookings (event_id, seat_no, email) VALUES (7, 'B4', 'sam@mail.example');
INSERT INTO bookings (event_id, seat_no, email) VALUES (7, 'B5', 'sam@mail.example');
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '7-B4' for key 'bookings.uq_event_seat'
Query OK, 1 row affected

2. Duplicates Already in the Table

A unique index can only be created if the existing data is already unique. Ana booked twice with the same email:

query.sql 
CREATE UNIQUE INDEX uq_email ON bookings (email);
Output 
ERROR 1062 (23000): Duplicate entry 'ana@mail.example' for key 'bookings.uq_email'
query.sql 
SELECT email, COUNT(*) AS bookings FROM bookings GROUP BY email HAVING COUNT(*) > 1;

Result: 1 row

emailbookings
ana@mail.example2

Here one email may book several times, so a unique index on email alone is the wrong rule. One email per event makes sense:

query.sql 
CREATE UNIQUE INDEX uq_event_email ON bookings (event_id, email);
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

3. NULLs Are Not Duplicates

A unique index allows many NULLs, because NULL means unknown. Several bookings can have no coupon, but each coupon code can be used only once:

nulls.sql 
CREATE UNIQUE INDEX uq_coupon ON bookings (coupon);
INSERT INTO bookings (event_id, seat_no, email, coupon) VALUES (8, 'A2', 'mo@mail.example', NULL);
INSERT INTO bookings (event_id, seat_no, email, coupon) VALUES (8, 'A3', 'zed@mail.example', 'EARLY10');
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 1 row affected
ERROR 1062 (23000): Duplicate entry 'EARLY10' for key 'bookings.uq_coupon'
query.sql 
SELECT COUNT(*) AS bookings_without_coupon FROM bookings WHERE coupon IS NULL;

Result: 1 row

bookings_without_coupon
4

4. Uniqueness Only for Some Rows

A cancelled booking shouldn't block the seat. MySQL has no partial (filtered) indexes, but a functional unique index can return NULL for rows that shouldn't count:

partial-unique.sql 
DROP INDEX uq_event_seat ON bookings;
CREATE UNIQUE INDEX uq_active_seat ON bookings
  ((IF(status = 'active', CONCAT(event_id, ':', seat_no), NULL)));

UPDATE bookings SET status = 'cancelled' WHERE event_id = 7 AND seat_no = 'B3';
INSERT INTO bookings (event_id, seat_no, email) VALUES (7, 'B3', 'new@mail.example');   -- seat freed
INSERT INTO bookings (event_id, seat_no, email) VALUES (7, 'B3', 'late@mail.example');  -- seat taken again
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected
ERROR 1062 (23000): Duplicate entry '7:B3' for key 'bookings.uq_active_seat'
query.sql 
SELECT event_id, seat_no, email, status FROM bookings WHERE event_id = 7 AND seat_no = 'B3';

Result: 2 rows

event_idseat_noemailstatus
7B3new@mail.exampleactive
7B3raj@mail.examplecancelled

5. Using a Unique Index for Upserts

Unique indexes make INSERT … ON DUPLICATE KEY UPDATE possible. A user rebooking the same event just updates their seat:

upsert.sql 
INSERT INTO bookings (event_id, seat_no, email) VALUES (8, 'A9', 'ana@mail.example') AS nb
ON DUPLICATE KEY UPDATE seat_no = nb.seat_no;
SELECT event_id, seat_no, email FROM bookings WHERE email = 'ana@mail.example' ORDER BY event_id;
Output 
Query OK, 2 rows affected

Result: 2 rows

event_idseat_noemail
7B2ana@mail.example
8A9ana@mail.example

6. Unique Index vs Unique Constraint vs Primary Key

UNIQUE INDEXUNIQUE constraintPRIMARY KEY
How createdCREATE UNIQUE INDEXUNIQUE (col) in CREATE/ALTER TABLEPRIMARY KEY (col)
Stored asUnique B-tree indexThe same unique indexClustered index (the table itself)
NULLsMany allowedMany allowedNot allowed
Per tableManyManyOne
Functional / expressionYesNoNo
In MySQL a UNIQUE constraint is a unique index; they appear the same in SHOW INDEX and are dropped with DROP INDEX.

7. Summary

  • CREATE UNIQUE INDEX prevents duplicate values and speeds up lookups.
  • Composite unique indexes enforce unique combinations, like (event, seat).
  • Existing duplicates must be fixed first; NULLs never count as duplicates.
  • Functional unique indexes can enforce uniqueness for only some rows.
  • Unique indexes power ON DUPLICATE KEY UPDATE.