00:00

Help Us Improve

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

MySQL UNIQUE

Last updated:

The UNIQUE constraint makes sure no two rows have the same value in a column, or the same combination of values in a group of columns. Typical uses: usernames, email addresses, phone numbers, PAN or ISBN codes, and "one vote per user per poll".

1. Creating a UNIQUE Column

A UNIQUE column rejects duplicate valuesA users table with a unique username column holds ravi_k, sonal and tom. Inserting another ravi_k is rejected; inserting a new name is accepted. Several rows may have NULL.usersidusername (UNIQUE)1ravi_k2sonal3tom4NULL5NULLINSERT 'ravi_k'✘ duplicateINSERT 'zara'✔ acceptedINSERT NULL✔ NULLs are not equal
Figure 1: UNIQUE allows every non-NULL value only once, but any number of NULLs.
unique.sql 
CREATE TABLE users (
  user_id  INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE,
  email    VARCHAR(80) UNIQUE,
  phone    VARCHAR(15)
);
INSERT INTO users (username, email) VALUES ('ravi_k', 'ravi@mail.example'), ('sonal', 'sonal@mail.example'), ('tom', NULL);
INSERT INTO users (username, email) VALUES ('ravi_k', 'ravi2@mail.example');
Output 
Query OK, 0 rows affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry 'ravi_k' for key 'users.username'

The error names the key, users.username. MySQL created a unique index for it automatically:

query.sql 
SHOW INDEX FROM users;

Result: 3 rows

Key_nameColumn_nameNon_unique
PRIMARYuser_id0
usernameusername0
emailemail0

2. UNIQUE and NULL

NULL means "unknown", and two unknowns aren't considered equal, so a UNIQUE column can hold many NULLs:

nulls.sql 
INSERT INTO users (username, email) VALUES ('uma', NULL), ('vik', NULL);
SELECT username, email FROM users WHERE email IS NULL;

Result: 3 rows

usernameemail
tomNULL
umaNULL
vikNULL
If a value must be both present and unique, use NOT NULL UNIQUE, as with username.

3. Case-Insensitive Duplicates

With MySQL's default case-insensitive collation, 'Ravi_K' counts as a duplicate of 'ravi_k'. That's usually what you want for usernames and emails:

query.sql 
INSERT INTO users (username) VALUES ('RAVI_K');
Output 
ERROR 1062 (23000): Duplicate entry 'RAVI_K' for key 'users.username'

Trailing spaces are significant in MySQL 8's default collation, but leading/trailing spaces in user input are best trimmed by the application before saving.

4. UNIQUE Across Several Columns

A composite unique key allows repeated values in each column, but not the same combination. Each user may vote once per poll:

composite.sql 
CREATE TABLE poll_votes (
  vote_id INT AUTO_INCREMENT PRIMARY KEY,
  poll_id INT NOT NULL,
  user_id INT NOT NULL,
  choice  CHAR(1) NOT NULL,
  CONSTRAINT uq_one_vote UNIQUE (poll_id, user_id)
);
INSERT INTO poll_votes (poll_id, user_id, choice) VALUES (1, 1, 'A'), (1, 2, 'B'), (2, 1, 'C');
INSERT INTO poll_votes (poll_id, user_id, choice) VALUES (1, 1, 'B');
Output 
Query OK, 0 rows affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '1-1' for key 'poll_votes.uq_one_vote'

5. Using UNIQUE for Insert-or-Update

A unique key lets INSERT … ON DUPLICATE KEY UPDATE change a vote instead of failing:

upsert.sql 
INSERT INTO poll_votes (poll_id, user_id, choice) VALUES (1, 1, 'B')
ON DUPLICATE KEY UPDATE choice = 'B';
SELECT poll_id, user_id, choice FROM poll_votes ORDER BY poll_id, user_id;

Result: 3 rows

poll_iduser_idchoice
11B
12B
21C

INSERT IGNORE skips duplicates silently (with a warning) instead.

6. Adding and Dropping UNIQUE

Adding a unique constraint fails if duplicates already exist:

add-fails.sql 
UPDATE users SET phone = '98450 12345' WHERE username IN ('sonal', 'tom');
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (phone);
Output 
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
ERROR 1062 (23000): Duplicate entry '98450 12345' for key 'users.uq_phone'
find-duplicates.sql 
SELECT phone, COUNT(*) AS times FROM users
WHERE phone IS NOT NULL GROUP BY phone HAVING COUNT(*) > 1;

Result: 1 row

phonetimes
98450 123452
add-then-drop.sql 
UPDATE users SET phone = '98450 67890' WHERE username = 'tom';
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (phone);
ALTER TABLE users DROP INDEX uq_phone;   -- a UNIQUE constraint is dropped as an index
Output 
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

7. UNIQUE vs PRIMARY KEY

PRIMARY KEYUNIQUE
How many per tableOneMany
NULL allowedNoYes (any number)
PurposeIdentify the rowPrevent duplicate business values
InnoDB storageClustered: decides row orderSecondary index
Referenced by foreign keysUsuallyPossible

See also MySQL Unique Index.

8. Practice Exercises

Exercise 1: Create a products table where the SKU is unique and each (brand, model) pair is unique.
solution.sql 
CREATE TABLE products (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  sku   VARCHAR(20) NOT NULL UNIQUE,
  brand VARCHAR(20) NOT NULL,
  model VARCHAR(30) NOT NULL,
  UNIQUE (brand, model)
);
SHOW INDEX FROM products;

Result: 4 rows

Key_nameSeq_in_indexColumn_name
PRIMARY1id
sku1sku
brand1brand
brand2model
Exercise 2: Which unique constraints exist in the social database?
solution.sql 
SELECT table_name, constraint_name
FROM information_schema.table_constraints
WHERE table_schema = 'social' AND constraint_type = 'UNIQUE';

Result: 5 rows

TABLE_NAMECONSTRAINT_NAME
poll_votesuq_one_vote
productsbrand
productssku
usersemail
usersusername

9. Summary

  • UNIQUE prevents duplicate values; MySQL enforces it with a unique index.
  • Many NULLs are allowed; add NOT NULL when the value is required.
  • Composite UNIQUE keys prevent duplicate combinations.
  • Default collations make the check case-insensitive.
  • Add with ADD CONSTRAINT … UNIQUE (after removing duplicates); drop with DROP INDEX.