by
SQL Tutorial › Database Design › UNIQUE

SQL UNIQUE Constraint

BeginnerLesson 52 of 698 min read6 queries run on MySQL 8.4

A UNIQUE constraint guarantees that no two rows share the same value in a column, or the same combination of values in several columns. Use it for natural identifiers that are not the primary key: email addresses, usernames, product codes, “one review per user per product”.

Advertisement
In this lesson
  • Make a column unique
  • Make a combination of columns unique
  • Understand how UNIQUE treats NULL
  • Add and drop unique constraints, and compare with PRIMARY KEY

A unique column

SQL
CREATE TABLE users (  user_id  INT PRIMARY KEY,  username VARCHAR(30) NOT NULL UNIQUE,  email    VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', 'asha@example.com'), (2, 'ravi', 'ravi@example.com');SELECT * FROM users;
ResultMySQL 8.4
mysql> CREATE TABLE users (
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', 'asha@example.com'), (2, 'ravi', …
Query OK, 2 rows affected
mysql> SELECT * FROM users
user_idusernameemail
1ashaasha@example.com
2raviravi@example.com
2 rows in set

A duplicate is rejected:

MySQL
CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', 'asha@example.com');INSERT INTO users VALUES (2, 'asha', 'other@example.com');
ResultMySQL 8.4
mysql> CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT …
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', 'asha@example.com')
Query OK, 1 row affected
mysql> INSERT INTO users VALUES (2, 'asha', 'other@example.com')
ERROR 1062 (23000): Duplicate entry 'asha' for key 'users.username'
How this query works, step by step
  1. The first row is inserted.
  2. The second row has a different id but the same username, so MySQL rejects it with error 1062 and names the key: users.username.
  3. With the default case-insensitive collation, 'Asha' would also count as a duplicate of 'asha'.

UNIQUE and NULL

A unique column may hold many NULLs, because NULL is not equal to anything, not even another NULL:

SQL
CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT NULL UNIQUE, email VARCHAR(100) UNIQUE);INSERT INTO users VALUES (1, 'asha', NULL), (2, 'ravi', NULL), (3, 'mona', NULL);SELECT COUNT(*) AS users_without_email FROM users WHERE email IS NULL;
ResultMySQL 8.4
mysql> CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(30) NOT …
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES (1, 'asha', NULL), (2, 'ravi', NULL), (3, 'mo…
Query OK, 3 rows affected
mysql> SELECT COUNT(*) AS users_without_email FROM users WHERE email IS NULL
users_without_email
3
1 row in set

Add NOT NULL if every row must have a distinct, real value.

Composite unique key

One review per customer per product: the pair must be unique, each column alone may repeat:

SQL
CREATE TABLE reviews (  review_id   INT PRIMARY KEY,  customer_id INT NOT NULL,  product_id  INT NOT NULL,  rating      INT NOT NULL,  CONSTRAINT uq_review UNIQUE (customer_id, product_id));INSERT INTO reviews VALUES (1, 1, 2, 5), (2, 1, 4, 4), (3, 2, 2, 3);SELECT * FROM reviews;
ResultMySQL 8.4
mysql> CREATE TABLE reviews (
Query OK, 0 rows affected
mysql> INSERT INTO reviews VALUES (1, 1, 2, 5), (2, 1, 4, 4), (3, 2, 2, 3)
Query OK, 3 rows affected
mysql> SELECT * FROM reviews
review_idcustomer_idproduct_idrating
1125
2144
3223
3 rows in set
MySQL
CREATE TABLE reviews (review_id INT PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, rating INT NOT NULL, CONSTRAINT uq_review UNIQUE (customer_id, product_id));INSERT INTO reviews VALUES (1, 1, 2, 5);INSERT INTO reviews VALUES (4, 1, 2, 1);
ResultMySQL 8.4
mysql> CREATE TABLE reviews (review_id INT PRIMARY KEY, customer_id INT NOT N…
Query OK, 0 rows affected
mysql> INSERT INTO reviews VALUES (1, 1, 2, 5)
Query OK, 1 row affected
mysql> INSERT INTO reviews VALUES (4, 1, 2, 1)
ERROR 1062 (23000): Duplicate entry '1-2' for key 'reviews.uq_review'
How this query works, step by step
  1. Customer 1 may review products 2 and 4, and customer 2 may also review product 2.
  2. A second review by customer 1 of product 2 repeats the pair (1, 2) and is rejected.

Add and drop UNIQUE

MySQL
ALTER TABLE customers ADD CONSTRAINT uq_customer_email UNIQUE (email);SHOW INDEX FROM customers WHERE Key_name = 'uq_customer_email';ALTER TABLE customers DROP INDEX uq_customer_email;
ResultMySQL 8.4
mysql> ALTER TABLE customers ADD CONSTRAINT uq_customer_email UNIQUE (email)
Query OK, 0 rows affected
mysql> SHOW INDEX FROM customers WHERE Key_name = 'uq_customer_email'
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
customers0uq_customer_email1emailA7NULLNULLYESBTREEYESNULL
1 row in set
mysql> ALTER TABLE customers DROP INDEX uq_customer_email
Query OK, 0 rows affected
How this query works, step by step
  1. In MySQL a unique constraint is implemented as a unique index, which also speeds up lookups by email.
  2. SHOW INDEX lists it; Non_unique = 0 means unique.
  3. It is dropped with DROP INDEX (or DROP CONSTRAINT in MySQL 8.0.19+).

UNIQUE vs PRIMARY KEY

PRIMARY KEYUNIQUE
Per tableonemany
NULL allowednoyes (many)
Purposethe row’s identity, target of foreign keysbusiness rules on other columns
InnoDB storagerows are stored in primary-key orderseparate index

Quick quiz

1. How many NULLs can a UNIQUE column hold in MySQL?

2. UNIQUE (customer_id, product_id) rejects…

3. How many UNIQUE constraints can a table have?

UNIQUE FAQ

What is a UNIQUE constraint in SQL?

A rule that no two rows may have the same value (or combination of values) in the constrained columns.

Can a UNIQUE column be NULL?

Yes. In MySQL a unique column can contain multiple NULLs; add NOT NULL to prevent them.

Is a UNIQUE constraint an index?

In MySQL, yes: each unique constraint creates a unique index, which also speeds up searches on those columns.