A database is only useful if its data is correct. A salary of −500, an order for a customer who doesn't exist, two employees with the same id, a date like 30 February — any of these silently corrupts reports, bills and decisions.
Integrity constraints are rules that the data must always satisfy. You declare them once, in the schema, and the DBMS checks them on every insert, update and delete — no matter which application, script or person makes the change. In this chapter we'll go through each type of constraint, see exactly how MySQL 8.4 reacts when one is violated (all error messages below are real), and learn the options for handling foreign key violations such as ON DELETE CASCADE.
We'll build on Relational Model (domains, NULL) and Keys in DBMS (primary, unique and foreign keys).
Why Let the DBMS Enforce Rules?
You could check everything in application code ("if salary <= 0, show an error"). But:
Many programs share one database. The web app might check salaries, but a quick admin script, a data import or a second mobile app might not.
Concurrency. Two users can each check "is this id free?", both see yes, and both insert. The DBMS checks uniqueness atomically.
The rule is written in one place, as part of the schema, instead of being repeated (and forgotten) in many programs.
An integrity constraint is a condition specified on a database schema that restricts the data that can be stored. A database state that satisfies all constraints is called a valid (or consistent) state.
Constraints are usually grouped like this:
Inherent (model-based): built into the relational model itself, e.g. no duplicate tuples, atomic values.
Schema-based (explicit): declared in DDL — domain, NOT NULL, key, entity integrity, referential integrity, CHECK. These are the focus of this chapter.
Application-based (semantic / business rules): rules too complex for simple DDL, such as "an employee's salary may not exceed their manager's". These need triggers, stored procedures or application code.
For the demos we use a practice copy of the SQL tutorial's company_db sample database, named dbms10_db (that's why that name appears inside some error messages). Its tables are departments, employees, customers and orders.
1. Domain Constraints
A domain constraint says that every value of an attribute must come from that attribute's domain: the right data type, a valid value of that type, and any further restrictions (range, list of allowed values, NULL or not).
Data type
salary is DECIMAL(10,2) and hire_date is DATE:
INSERT INTO employees VALUES (11, 'Tanvi Shah', 'tanvi@company.com', 10, 1, 'abc', '2024-05-01', 'Surat');
ERROR 1366 (HY000): Incorrect decimal value: 'abc' for column 'salary' at row 1
INSERT INTO employees VALUES (11, 'Tanvi Shah', 'tanvi@company.com', 10, 1, 60000, '2024-02-30', 'Surat');
ERROR 1292 (22007): Incorrect date value: '2024-02-30' for column 'hire_date' at row 1
Watch out: MySQL's SQL mode
These are errors because MySQL 8 runs in strict mode by default (STRICT_TRANS_TABLES is part of the default sql_mode). If strict mode is switched off, MySQL "fixes" bad values instead and only gives warnings:
CREATE TABLE ns (d DATE, n DECIMAL(6,2));
SET SESSION sql_mode = '';
INSERT INTO ns VALUES ('2024-02-30', 'abc'); -- succeeds!
SHOW WARNINGS;
SELECT * FROM ns;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
| Warning | 1366 | Incorrect decimal value: 'abc' for column 'n' at row 1 |
+---------+------+--------------------------------------------------------+
+------------+------+
| d | n |
+------------+------+
| 0000-00-00 | 0.00 |
+------------+------+
Garbage got stored. Keep strict mode on (and if you tried this yourself, restore it with SET SESSION sql_mode = DEFAULT; before continuing, otherwise the later examples will not raise errors).
NOT NULL
A NOT NULL constraint removes NULL from the attribute's domain. departments.location is NOT NULL:
INSERT INTO departments VALUES (60, 'Finance', NULL);
ERROR 1048 (23000): Column 'location' cannot be null
CHECK
A CHECK constraint narrows the domain with a condition. Let's add "salary must be positive" to the existing table:
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
UPDATE employees SET salary = -500 WHERE emp_id = 5;
ERROR 3819 (HY000): Check constraint 'chk_salary' is violated.
A CHECK can compare columns of the same row:
CREATE TABLE product (
pid INT PRIMARY KEY,
price DECIMAL(8,2) CHECK (price > 0),
discount DECIMAL(8,2),
CONSTRAINT chk_disc CHECK (discount < price)
);
INSERT INTO product VALUES (1, 500, 50); -- OK
INSERT INTO product VALUES (2, 500, 600); -- discount bigger than price
ERROR 3819 (HY000): Check constraint 'chk_disc' is violated.
Now a subtle point that is a favourite interview question. What happens with NULL?
INSERT INTO product VALUES (3, NULL, NULL);
SELECT * FROM product;
+-----+--------+----------+
| pid | price | discount |
+-----+--------+----------+
| 1 | 500.00 | 50.00 |
| 3 | NULL | NULL |
+-----+--------+----------+
It was accepted. NULL > 0 is UNKNOWN, not FALSE, and a CHECK constraint only rejects a row when the condition is FALSE. (A WHERE clause is the opposite: it keeps only rows where the condition is TRUE.) If NULL shouldn't be allowed, add NOT NULL as well.
Version note: MySQL only enforces CHECK constraints from version 8.0.16 onwards. Older versions parsed the CHECK syntax and silently ignored it, so on an old server the −500 salary would have been stored. If you maintain an old schema, don't assume its CHECKs ever worked.
2. Key Constraints (Uniqueness)
A key constraint says that no two tuples may have the same value for a candidate key. In SQL it is declared with
PRIMARY KEYorUNIQUE.
INSERT INTO departments VALUES (10, 'Finance', 'Chennai'); -- dept_id 10 already exists
ERROR 1062 (23000): Duplicate entry '10' for key 'departments.PRIMARY'
INSERT INTO departments VALUES (60, 'Sales', 'Chennai'); -- dept_name is UNIQUE
ERROR 1062 (23000): Duplicate entry 'Sales' for key 'departments.dept_name'
As shown in Keys in DBMS, a UNIQUE column still accepts several NULLs in MySQL, because NULLs are not treated as equal to each other.
3. Entity Integrity Constraint
Entity integrity: no attribute of a primary key may be NULL.
Why? The primary key is how we identify a tuple. If it were NULL, we couldn't distinguish that row from another, and foreign keys couldn't point to it. In MySQL, primary key columns are automatically made NOT NULL:
INSERT INTO departments VALUES (NULL, 'Finance', 'Chennai');
ERROR 1048 (23000): Column 'dept_id' cannot be null
For a composite primary key, every column must be non-NULL — not just "at least one".
Key constraint vs entity integrity: the key constraint is about uniqueness, entity integrity is about non-NULL. A primary key needs both; a UNIQUE key needs only the first.
4. Referential Integrity Constraint
Referential integrity: if a tuple in relation R1 has a foreign key referring to relation R2, then the foreign key value must either match the primary (or unique) key of some existing tuple in R2, or be entirely NULL.
In plain words: no dangling references. An employee can't belong to department 99 if department 99 doesn't exist. But an employee may have no department (NULL) — like Ishita Gupta in our data, or guest order 1008 with no customer.
The four ways to break referential integrity
R1 = employees (child, referencing), R2 = departments (parent, referenced).
(a) Insert a child row with a non-existent parent:
INSERT INTO employees VALUES (11, 'Tanvi Shah', 'tanvi@company.com', 99, 1, 60000, '2024-05-01', 'Surat');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbms10_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
(b) Update a child's foreign key to a non-existent parent:
UPDATE employees SET dept_id = 99 WHERE emp_id = 5;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbms10_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
(c) Delete a parent row that still has children: HR (dept 30) has two employees.
DELETE FROM departments WHERE dept_id = 30;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbms10_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
(d) Change a parent's key value that children refer to:
UPDATE departments SET dept_id = 35 WHERE dept_id = 30;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbms10_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
Deleting a parent with no children is fine: DELETE FROM departments WHERE dept_id = 50; (Research has no employees) succeeds.
Notice the error numbers: 1452 means the problem is on the child side (inserting/updating a reference), 1451 means it's on the parent side (removing/changing what is referenced).
Referential actions: what should happen to the children?
Cases (a) and (b) are always rejected — there's no sensible way to "fix" a reference to nothing. But for cases (c) and (d), deleting or updating a parent, you can tell the DBMS what to do with the children using ON DELETE and ON UPDATE:
Action | Effect on child rows when the parent is deleted/updated | Typical use |
|---|---|---|
| Reject the parent operation | Don't delete a course students are enrolled in |
| Standard SQL: reject, but checked at the end of the statement. In MySQL InnoDB it behaves exactly like RESTRICT | Default when nothing is specified |
| Delete the children too / update their foreign key to the new value | Weak entities, line items of an order, a student's enrolments |
| Set the children's foreign key to NULL (column must allow NULL) | Optional links: advisor leaves, student stays |
| Set the children's foreign key to its default value (standard SQL) | Not implemented by MySQL InnoDB — see below |
If you write no ON DELETE clause, MySQL uses the default (NO ACTION, i.e. reject). That's what happened in cases (c) and (d) above.
Seeing CASCADE, RESTRICT and SET NULL in action
CREATE TABLE course (
course_id VARCHAR(10) PRIMARY KEY,
title VARCHAR(50) NOT NULL,
credits INT NOT NULL CHECK (credits BETWEEN 1 AND 5)
);
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
advisor_course VARCHAR(10),
FOREIGN KEY (advisor_course) REFERENCES course(course_id) ON DELETE SET NULL
);
CREATE TABLE enrolment (
roll_no INT,
course_id VARCHAR(10),
grade CHAR(2),
PRIMARY KEY (roll_no, course_id),
FOREIGN KEY (roll_no) REFERENCES student(roll_no) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE RESTRICT ON UPDATE CASCADE
);
INSERT INTO course VALUES ('CS101','Programming in C',4), ('CS201','DBMS',4), ('CS202','Operating Systems',3);
INSERT INTO student VALUES (1,'Asha','CS201'), (2,'Bilal','CS202'), (3,'Chitra',NULL);
INSERT INTO enrolment VALUES (1,'CS101','A'), (1,'CS201','B'), (2,'CS201','A'), (3,'CS202','C');
(Along the way, the CHECK on credits did its job: INSERT INTO course VALUES ('CS999','Mega Course',8) failed with ERROR 3819 (HY000): Check constraint 'course_chk_1' is violated. — MySQL generates names like course_chk_1 for unnamed CHECKs.)
ON DELETE CASCADE — Asha leaves the college:
DELETE FROM student WHERE roll_no = 1;
SELECT * FROM enrolment;
+---------+-----------+-------+
| roll_no | course_id | grade |
+---------+-----------+-------+
| 2 | CS201 | A |
| 3 | CS202 | C |
+---------+-----------+-------+
Both of Asha's enrolments disappeared automatically.
ON DELETE RESTRICT — try to drop DBMS while Bilal is enrolled in it:
DELETE FROM course WHERE course_id = 'CS201';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbms10_db`.`enrolment`, CONSTRAINT `enrolment_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE CASCADE)
ON UPDATE CASCADE — the course code is renumbered:
UPDATE course SET course_id = 'CS250' WHERE course_id = 'CS201';
SELECT * FROM enrolment;
+---------+-----------+-------+
| roll_no | course_id | grade |
+---------+-----------+-------+
| 2 | CS250 | A |
| 3 | CS202 | C |
+---------+-----------+-------+
Notice that delete was restricted but update cascaded — the two actions are independent.
ON DELETE SET NULL — Bilal's advisor course is CS202. Once its enrolments are removed, CS202 can be deleted, and Bilal's advisor_course becomes NULL instead of blocking the delete:
DELETE FROM enrolment WHERE course_id = 'CS202';
DELETE FROM course WHERE course_id = 'CS202';
SELECT * FROM student;
+---------+--------+----------------+
| roll_no | name | advisor_course |
+---------+--------+----------------+
| 2 | Bilal | NULL |
| 3 | Chitra | NULL |
+---------+--------+----------------+
SET DEFAULT in MySQL
The MySQL manual states that InnoDB does not support SET DEFAULT. In our MySQL 8.4 test, a table declared with ON DELETE SET DEFAULT was created without complaint, but deleting a referenced parent row was simply refused with the usual ERROR 1451, and the child was not changed. Don't rely on SET DEFAULT in MySQL; PostgreSQL and SQL Server do implement it.
Choosing an action
Ask: "Can the child meaningfully exist without this parent?"
No, it's part of the parent (order items, a weak entity's rows, a student's enrolments) →
CASCADE.Yes, the link is optional (advisor, assigned salesperson) →
SET NULL.The child is important history that must not be lost (invoices of a customer, grades in a course) →
RESTRICT, and make the user deal with the children explicitly.
Be careful with CASCADE: one delete can ripple through many tables. Deleting a department with cascade into employees, and from employees into their orders, could wipe out a lot of data with one statement.
5. General Constraints: Assertions and Triggers
Some rules involve several rows or several tables, for example "a department can have at most 50 employees" or "every employee's department must be in the same city as the employee". A CHECK in MySQL can only look at the current row; it can't run a subquery:
ALTER TABLE employees ADD CHECK (dept_id IN (SELECT dept_id FROM departments));
ERROR 3815 (HY000): An expression of a check constraint 'employees_chk_1' contains disallowed function.
The SQL standard defines assertions for such database-wide conditions:
CREATE ASSERTION max_emp CHECK ((SELECT COUNT(*) FROM employees) < 1000);
But practically no major DBMS implements them, because checking an arbitrary query after every change is very expensive. MySQL just reports a syntax error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASSERTION max_emp CHECK ((SELECT COUNT(*) FROM employees) < 1000)' at line 1
In practice, multi-row and multi-table rules are enforced with triggers (code that runs automatically before/after an insert, update or delete and can reject the change) or in the application. Triggers are covered in the SQL tutorial.
Which Operations Can Violate Which Constraint?
Constraint | INSERT | DELETE | UPDATE |
|---|---|---|---|
Domain / NOT NULL / CHECK | Yes | No | Yes |
Key (uniqueness) | Yes | No | Yes (if a key column changes) |
Entity integrity | Yes | No | Yes (setting PK to NULL) |
Referential integrity | Yes (child row) | Yes (parent row) | Yes (child FK or parent key) |
A neat way to remember it: delete can only break referential integrity, because removing a row can't create a bad value or a duplicate — it can only leave someone else pointing at nothing.
Naming and Managing Constraints
Give important constraints names; error messages become clearer (compare chk_salary with employees_chk_1) and you can drop them later:
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
ALTER TABLE employees DROP CHECK chk_salary; -- MySQL 8.0.16+
ALTER TABLE employees DROP FOREIGN KEY employees_ibfk_1;
You can list every constraint from the catalog. For the original company_db:
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'company_db'
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;
+-------------+------------------+-----------------+
| TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-------------+------------------+-----------------+
| customers | PRIMARY | PRIMARY KEY |
| departments | PRIMARY | PRIMARY KEY |
| departments | dept_name | UNIQUE |
| employees | employees_ibfk_1 | FOREIGN KEY |
| employees | employees_ibfk_2 | FOREIGN KEY |
| employees | PRIMARY | PRIMARY KEY |
| employees | email | UNIQUE |
| orders | orders_ibfk_1 | FOREIGN KEY |
| orders | PRIMARY | PRIMARY KEY |
+-------------+------------------+-----------------+
(NOT NULL is a column property in MySQL, so it doesn't appear in this list.)
Common Mistakes
Assuming CHECK rejects NULL. A CHECK only fails when the condition is FALSE; NULL makes it UNKNOWN, which passes. Add NOT NULL.
Expecting CHECKs to work on MySQL older than 8.0.16. They were ignored.
Turning off strict mode to "make an import work" — invalid values get silently converted.
Using ON DELETE SET NULL on a NOT NULL column. The two contradict each other; MySQL rejects such a foreign key definition.
Using CASCADE everywhere. It's convenient until one delete removes years of data.
Confusing key constraint with entity integrity. Uniqueness vs not-NULL.
Thinking a NULL foreign key violates referential integrity. It doesn't; NULL means "no reference".
Interview and Exam Questions
What are the main types of integrity constraints?
Domain constraints (type, NOT NULL, CHECK), key constraints (uniqueness of candidate keys), entity integrity (primary key not NULL), and referential integrity (foreign keys must match an existing key or be NULL). Plus general constraints such as assertions and triggers for complex business rules.
Difference between entity integrity and referential integrity?
Entity integrity is about a table's own primary key: it can't be NULL. Referential integrity is about links between tables: every non-NULL foreign key value must exist as a key in the referenced table.
Which operations can violate referential integrity?
Inserting or updating a child row with a non-existent parent key, and deleting or updating a parent row that is still referenced. Delete can violate only referential integrity.
Explain ON DELETE CASCADE vs ON DELETE SET NULL.
CASCADE deletes the child rows along with the parent. SET NULL keeps the child rows but sets their foreign key to NULL. Use CASCADE when the child cannot exist without the parent, SET NULL when the link is optional.
Is RESTRICT the same as NO ACTION?
In the SQL standard, NO ACTION is checked at the end of the statement (and can be deferred) while RESTRICT is checked immediately. In MySQL InnoDB they behave the same: the parent operation is rejected immediately. NO ACTION is the default.
Does a CHECK (price > 0) constraint allow NULL price?
Yes. NULL > 0 evaluates to UNKNOWN, and a CHECK only rejects rows where it evaluates to FALSE. Use NOT NULL to forbid NULL.
How would you enforce "a department can have at most 50 employees"?
It involves counting rows, so a single-row CHECK can't do it. The standard has assertions, but MySQL doesn't support them; use a BEFORE INSERT/UPDATE trigger (or application logic) that counts and rejects the change.
Summary
Integrity constraints are rules declared in the schema and enforced by the DBMS on every change.
Domain constraints: correct type, valid value, NOT NULL, CHECK. MySQL enforces CHECK from 8.0.16; CHECK passes on NULL.
Key constraint: candidate key values are unique. Entity integrity: primary key values are never NULL.
Referential integrity: foreign keys match an existing key or are NULL. Parent-side violations can be handled with RESTRICT / NO ACTION, CASCADE or SET NULL (SET DEFAULT isn't supported by InnoDB).
Multi-row/multi-table rules need assertions (standard, rarely implemented) or triggers.
With relations, keys and constraints in place, we're ready to query relations formally. The next chapter, Relational Algebra, introduces the operations (select, project, join and more) that every SQL query is built from.