A constraint is a rule the database enforces on every insert and update, whatever program sends the data. Application code can have bugs, and a manual fix late at night can skip validation. A constraint cannot be skipped. MySQL 8.0 supports six kinds:
| Constraint | Rule it enforces | Error when broken |
|---|---|---|
NOT NULL | The column must have a value | 1048 |
DEFAULT | Fills a value when the insert leaves the column out | none (it is a fallback) |
UNIQUE | No two rows share the value (multiple NULLs are allowed) | 1062 |
PRIMARY KEY | UNIQUE + NOT NULL; one per table; identifies the row | 1062 |
FOREIGN KEY | The value must exist in the parent table | 1452 / 1451 |
CHECK | A condition must be true (enforced from 8.0.16) | 3819 |
One table, all the rules
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(40) NOT NULL UNIQUE
);
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(80) NOT NULL,
full_name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2) NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'active',
CONSTRAINT uq_emp_email UNIQUE (email),
CONSTRAINT chk_salary_positive CHECK (salary > 0),
CONSTRAINT chk_status CHECK (status IN ('active','on_leave','exited')),
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
INSERT INTO departments VALUES (10,'Finance'), (20,'Support');
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('neha@example.com','Neha Rawat',10,52000);
Naming constraints (CONSTRAINT uq_emp_email ...) is optional, but the name appears in error messages and makes them easy to trace. Now try to break each rule.
Breaking the rules on purpose
UNIQUE: a second row with the same email
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('neha@example.com','Neha R.',20,48000);
ERROR 1062 (23000): Duplicate entry 'neha@example.com' for key 'employees.uq_emp_email'
NOT NULL: a missing name
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com', NULL, 20, 41000);
ERROR 1048 (23000): Column 'full_name' cannot be null
CHECK: a negative salary, an unknown status
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',20,-5);
INSERT INTO employees (email, full_name, dept_id, salary, status)
VALUES ('arjun@example.com','Arjun Pillai',20,41000,'retired');
ERROR 3819 (HY000): Check constraint 'chk_salary_positive' is violated.
ERROR 3819 (HY000): Check constraint 'chk_status' is violated.
FOREIGN KEY: a department that does not exist
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',30,41000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`con`.`employees`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
The rows that did get in
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',20,41000);
INSERT INTO employees (email, full_name, salary)
VALUES ('meera@example.com','Meera Das',39000);
SELECT emp_id, email, dept_id, salary, status FROM employees;
| emp_id | dept_id | salary | status | |
|---|---|---|---|---|
| 1 | neha@example.com | 10 | 52000.00 | active |
| 4 | arjun@example.com | 20 | 41000.00 | active |
| 5 | meera@example.com | NULL | 39000.00 | active |
Three details are worth noticing:
- emp_id jumped from 1 to 4. Some failed inserts still used up an
AUTO_INCREMENTvalue. Gaps are normal; never rely on IDs being continuous. - Meera has
dept_idNULL and the foreign key accepted it. A foreign key only checks non-NULL values. AddNOT NULLif every employee must have a department. statusis 'active' for everyone because of theDEFAULT.
The parent side of a foreign key
DELETE FROM departments WHERE dept_id = 20;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ...
The default action is to refuse. You can choose ON DELETE CASCADE, SET NULL or RESTRICT when you define the key. Each option is demonstrated in primary key vs foreign key in MySQL.
Listing and changing constraints
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'con' AND TABLE_NAME = 'employees'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
|---|---|
| chk_salary_positive | CHECK |
| chk_status | CHECK |
| fk_emp_dept | FOREIGN KEY |
| PRIMARY | PRIMARY KEY |
| uq_emp_email | UNIQUE |
(con is the database name used here.) NOT NULL and DEFAULT are column properties, so they appear in DESCRIBE employees instead. To change a CHECK, drop it and add it again:
ALTER TABLE employees DROP CHECK chk_status;
ALTER TABLE employees ADD CONSTRAINT chk_status
CHECK (status IN ('active','on_leave','exited','retired'));
Adding a constraint to a table that already has data fails if any existing row breaks it, so clean the data first.
Gotchas
- On MySQL 5.7 and early 8.0 releases,
CHECKwas accepted and ignored. RunSELECT VERSION();before trusting it. - If the server is not in strict SQL mode, some bad values are converted with a warning instead of rejected. Keep
STRICT_TRANS_TABLESon; it is part of the defaultsql_modein MySQL 8.0. - Foreign keys need InnoDB on both tables, and matching column types.
- A UNIQUE column allows many NULLs. If "no value" should also be unique, add NOT NULL.
For where constraints sit in the whole CREATE TABLE statement, see how to create a table in MySQL. To decide how tables should be split before you add keys, read normalization: 1NF, 2NF and 3NF.