UNIQUE and CHECK constraints
45 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A UNIQUE constraint stops a value from repeating in a column, and a CHECK constraint refuses any value that breaks a rule you write, such as price > 0. A constraint is simply a rule stored with the table that MySQL checks on every insert and update, whichever application sends the data.
UNIQUE on one column
A UNIQUE constraint makes MySQL refuse a value that already exists in that column. Unlike the primary key, a table can have many UNIQUE constraints.
Two people signing up with the same email would break password reset. Example 1 makes the email unique and shows the repeat refused.
Example 1 · One account per email
CREATE TABLE app_users ( user_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, username VARCHAR(30) NOT NULL UNIQUE);
DESCRIBE app_users;
INSERT INTO app_users (email, username) VALUES ('asha.rao@example.com', 'asha');INSERT INTO app_users (email, username) VALUES ('ravi.kumar@example.com', 'ravi_k');
-- Asha tries to sign up again with a capital AINSERT INTO app_users (email, username) VALUES ('Asha.Rao@example.com', 'asha2');+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | email | varchar(100) | NO | UNI | NULL | | | username | varchar(30) | NO | UNI | NULL | | +----------+--------------+------+-----+---------+----------------+ ERROR 1062 (23000): Duplicate entry 'Asha.Rao@example.com' for key 'app_users.email'
What this code does
DESCRIBEshowsUNIforemailandusername: one table, two UNIQUE constraints, plus the primary key.- The third insert has a new username but the same email in different case. The collation treats them as equal, so MySQL refuses it with error 1062 and names the key
app_users.email.
Write it after the type: email VARCHAR(100) NOT NULL UNIQUE.
UNIQUE solves a problem the primary key cannot. Most tables use a surrogate user_id as the key, but the email, the mobile number or the username must still be unique. Without UNIQUE, the same person can sign up twice, and "forgot password" no longer knows which account to reset. A repeat gives the same error 1062 as a primary key, with the column's name in place of PRIMARY.
utf8mb4_0900_ai_ci, 'Ravi@x.com' and 'ravi@x.com' count as the same value, so UNIQUE refuses the second one.UNIQUE on several columns
A composite UNIQUE constraint makes a combination of columns unique, while each column alone may repeat. Write it as a separate line, such as UNIQUE (train_no, journey_date, coach, berth).
A berth can be sold only once per train per date. Example 2 puts one UNIQUE rule on the four columns together.
Example 2 · One berth, one passenger
CREATE TABLE berth_bookings ( booking_id INT AUTO_INCREMENT PRIMARY KEY, train_no INT NOT NULL, journey_date DATE NOT NULL, coach VARCHAR(4) NOT NULL, berth TINYINT NOT NULL, passenger VARCHAR(40) NOT NULL, UNIQUE (train_no, journey_date, coach, berth));
INSERT INTO berth_bookings (train_no, journey_date, coach, berth, passenger)VALUES (12627, '2025-08-15', 'S4', 21, 'Meena'), (12627, '2025-08-16', 'S4', 21, 'Kabir'), -- next day: fine (12627, '2025-08-15', 'S4', 22, 'Imran'); -- next berth: fine
SELECT * FROM berth_bookings ORDER BY booking_id;
INSERT INTO berth_bookings (train_no, journey_date, coach, berth, passenger)VALUES (12627, '2025-08-15', 'S4', 21, 'Rohit');+------------+----------+--------------+-------+-------+-----------+ | booking_id | train_no | journey_date | coach | berth | passenger | +------------+----------+--------------+-------+-------+-----------+ | 1 | 12627 | 2025-08-15 | S4 | 21 | Meena | | 2 | 12627 | 2025-08-16 | S4 | 21 | Kabir | | 3 | 12627 | 2025-08-15 | S4 | 22 | Imran | +------------+----------+--------------+-------+-------+-----------+ ERROR 1062 (23000): Duplicate entry '12627-2025-08-15-S4-21' for key 'berth_bookings.train_no'
What this code does
- The first three bookings differ in at least one of the four columns, so all are stored, even though the train and coach repeat.
- Rohit asks for berth 21 in S4 on the same train and date as Meena, so the four-column combination repeats and MySQL refuses it.
- The unnamed constraint is called
train_noafter its first column, so the message readsfor key 'berth_bookings.train_no'.
A composite UNIQUE is the natural partner of a surrogate key. A booking gets its own booking_id, and the UNIQUE line says the real rule. This is how a railway system prevents two passengers being given the same berth. When you do not name it, MySQL names the constraint after its first column. So the error in Example 2 says for key 'berth_bookings.train_no', which sounds like a problem with the train number. The last section fixes that with names.
UNIQUE and NULL
UNIQUE ignores NULL: a UNIQUE column can hold any number of NULLs, but each real value only once, because two unknown values are not treated as equal.
Example 3 stores many members with no phone number, but refuses a phone number that is used twice.
Example 3 · Many NULLs, but no repeated number
CREATE TABLE gym_members ( member_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, phone VARCHAR(15) UNIQUE -- optional, but never shared);
INSERT INTO gym_members (name, phone) VALUES ('Divya', '9876500007');INSERT INTO gym_members (name) VALUES ('Arjun'); -- no phoneINSERT INTO gym_members (name) VALUES ('Meena'); -- no phone either
SELECT * FROM gym_members ORDER BY member_id;
INSERT INTO gym_members (name, phone) VALUES ('Rahul', '9876500007');+-----------+-------+------------+ | member_id | name | phone | +-----------+-------+------------+ | 1 | Divya | 9876500007 | | 2 | Arjun | NULL | | 3 | Meena | NULL | +-----------+-------+------------+ ERROR 1062 (23000): Duplicate entry '9876500007' for key 'gym_members.phone'
What this code does
- Arjun and Meena both have NULL phones, and UNIQUE allows that: NULL means "unknown", so two NULLs never clash.
- Rahul gives Divya's number, a real value that already exists, so the insert fails with error 1062.
Ignoring NULL is useful for optional values. Many customers may skip the phone number (NULL), but two customers can never give the same number. In the practice database, customers.email is NOT NULL UNIQUE, while an optional phone could be just UNIQUE. Watch composite UNIQUE: if any part is NULL, the row never clashes. (1, NULL) can be stored many times. Make every part NOT NULL when the rule must always hold.
CHECK rules (MySQL 8.0.16 and later)
A CHECK constraint is a true-or-false rule that every row must pass, such as price > 0. MySQL refuses any insert or update that makes the rule false, with ERROR 3819.
A price below zero should never be stored. Example 4 adds CHECK rules to a product table, and Example 5 compares two columns.
Example 4 · CHECK rules on a product table
CREATE TABLE shop_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price > 0), discount TINYINT NOT NULL DEFAULT 0 CHECK (discount BETWEEN 0 AND 90));
INSERT INTO shop_items (name, price, discount) VALUES ('Redmi Note 13', 17999.00, 10);INSERT INTO shop_items (name, price) VALUES ('Clean Code', 499.00);
SELECT * FROM shop_items ORDER BY item_id;
-- A typing mistake: 95 instead of 9INSERT INTO shop_items (name, price, discount) VALUES ('boAt Airdopes 141', 1299.00, 95);+---------+---------------+----------+----------+ | item_id | name | price | discount | +---------+---------------+----------+----------+ | 1 | Redmi Note 13 | 17999.00 | 10 | | 2 | Clean Code | 499.00 | 0 | +---------+---------------+----------+----------+ ERROR 3819 (HY000): Check constraint 'shop_items_chk_2' is violated.
What this code does
- Both rules pass for the first two rows.
BETWEEN 0 AND 90includes both ends, so 0 and 90 are allowed. - A 95% discount breaks the second CHECK, so MySQL stops the insert with error 3819 (on MySQL 8.0.16 and later).
- The rules have no names, so MySQL calls them
shop_items_chk_1andshop_items_chk_2, numbered in the order they appear.
Example 5 · A CHECK that compares two columns
CREATE TABLE guest_house_stays ( stay_id INT AUTO_INCREMENT PRIMARY KEY, guest VARCHAR(40) NOT NULL, check_in DATE NOT NULL, check_out DATE NOT NULL, CHECK (check_out > check_in));
INSERT INTO guest_house_stays (guest, check_in, check_out)VALUES ('Parent of Aditya', '2025-09-12', '2025-09-14');
SELECT * FROM guest_house_stays ORDER BY stay_id;
-- The two dates were swapped in the formINSERT INTO guest_house_stays (guest, check_in, check_out)VALUES ('Parent of Harini', '2025-09-20', '2025-09-18');+---------+------------------+------------+------------+ | stay_id | guest | check_in | check_out | +---------+------------------+------------+------------+ | 1 | Parent of Aditya | 2025-09-12 | 2025-09-14 | +---------+------------------+------------+------------+ ERROR 3819 (HY000): Check constraint 'guest_house_stays_chk_1' is violated.
What this code does
- A rule that uses two columns is written on its own line after the columns, like a composite key.
- The second stay ends before it starts, so
check_out > check_inis false and MySQL refuses the row.
If the rule is false for a new or changed row, MySQL stops the statement with ERROR 3819: Check constraint ... is violated.
Use it for rules about one row: a price above zero, marks from 0 to 100, a check-out date after the check-in date. A rule on one column can sit after that column; a rule that compares two columns goes on its own line after the columns. MySQL enforces CHECK only from version 8.0.16. Older versions read the CHECK and silently ignored it, so old tutorials may claim "MySQL has no CHECK". On 8.4 it works as shown here.
Find the bug
Find the broken CHECK
One line of each table definition is wrong.
Stock must never be negative.
Round 1 of 2CHECK (price > 0) accepts a NULL price; add NOT NULL when the value is required. A CHECK also cannot call functions that change over time, such as CURDATE().Naming constraints so errors are readable
A constraint name is the label MySQL prints in the error when a rule is broken. Give one with the word CONSTRAINT, as in CONSTRAINT chk_products_price CHECK (price > 0).
Example 6 gives every rule a name, so each error says which rule broke.
Example 6 · Named constraints give readable errors
CREATE TABLE shop_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(20) NOT NULL, price DECIMAL(10,2) NOT NULL, discount TINYINT NOT NULL DEFAULT 0, CONSTRAINT uq_shop_items_sku UNIQUE (sku), CONSTRAINT chk_shop_items_price CHECK (price > 0), CONSTRAINT chk_shop_items_discount CHECK (discount BETWEEN 0 AND 90));
SELECT constraint_name, constraint_typeFROM information_schema.table_constraintsWHERE table_schema = DATABASE() AND table_name = 'shop_items'ORDER BY constraint_name;
INSERT INTO shop_items (sku, price, discount) VALUES ('BOAT-141', 1299.00, 95);+-------------------------+-----------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-------------------------+-----------------+ | chk_shop_items_discount | CHECK | | chk_shop_items_price | CHECK | | PRIMARY | PRIMARY KEY | | uq_shop_items_sku | UNIQUE | +-------------------------+-----------------+ ERROR 3819 (HY000): Check constraint 'chk_shop_items_discount' is violated.
What this code does
information_schema.table_constraintsis MySQL's own list of every constraint; the query shows this table's four, by name.- The same mistake as Example 4 now reports
chk_shop_items_discount, which says exactly which rule failed. - Each rule sits on its own
CONSTRAINT name ...line after the columns. MySQL also acceptsCONSTRAINT name CHECK (...)right after a single column.
A UNIQUE constraint is named the same way: CONSTRAINT uq_users_email UNIQUE (email).
Without a name, MySQL invents one: t_chk_1, t_chk_2 for CHECKs and the first column's name for UNIQUE. An error like Check constraint 'shop_items_chk_2' is violated makes you open the table to find out which rule failed. chk_shop_items_discount tells you at once, and your application can even map it to a friendly message. A common pattern is pk_, uq_, chk_ and fk_ + table + column. Include the table: CHECK names must be unique in the whole database, not just in the table. Named constraints are also easy to remove: ALTER TABLE t DROP CHECK chk_name; and, because UNIQUE is stored as an index, ALTER TABLE t DROP INDEX uq_name;.
Next step
Check your understanding
11 quick questions