The idea: the database refuses rows that break the rules
An integrity constraint is a rule that is stored with a table. MariaDB checks it on every INSERT, UPDATE and DELETE, from every client. A row that breaks a rule never gets in. The statement stops with an error number, and InnoDB undoes everything the statement had done so far.
The canvas uses three tables of a small shop. A customers row can have many orders, and an order can have many order_items. Ana (1) has orders 10 and 11, and Ben (2) has order 12.
CREATE TABLE customers (
id INT PRIMARY KEY,
email VARCHAR(40) NOT NULL,
name VARCHAR(20) NOT NULL,
UNIQUE KEY email (email));
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NULL,
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE order_items (
order_id INT NOT NULL,
line INT NOT NULL,
qty INT NOT NULL,
PRIMARY KEY (order_id, line),
CONSTRAINT chk_qty CHECK (qty > 0),
CONSTRAINT fk_items_order FOREIGN KEY (order_id)
REFERENCES orders (id) ON DELETE CASCADE);
Five kinds of constraint, five errors
| constraint | the rule | error when it fails |
|---|---|---|
NOT NULL | the column must have a value | 1048 Column 'name' cannot be null |
CHECK | an expression on the row must not be false | 4025 CONSTRAINT `chk_qty` failed for `shop`.`order_items` |
PRIMARY KEY | unique and NOT NULL; one per table | 1062 Duplicate entry '1' for key 'PRIMARY' |
UNIQUE | no two rows share a value (NULLs may repeat) | 1062 Duplicate entry 'ana@x.com' for key 'email' |
FOREIGN KEY | a child value must exist in the parent | 1452 on the child side, 1451 on the parent side |
All of them have SQLSTATE 23000, "integrity constraint violation". The error text names the column, the value or the constraint, so it pays to give constraints names such as chk_qty.
Demo: PRIMARY KEY and UNIQUE refuse duplicates inserts Cy (3) and then tries id 1 again and Ana's email again: 3 customers at the end, two errors 1062. Demo: NOT NULL and CHECK tries a NULL name (1048) and an item with qty 0 (4025), then qty 4 gets in.
NOT NULL and CHECK are tested by the server before the row reaches InnoDB. InnoDB tests the keys and the foreign keys while it writes the row, using their indexes. A foreign key needs an index on the child column, and InnoDB creates one if there is none. The parent column must be a primary key or have an index. CHECK constraints are enforced since MariaDB 10.2.1; older versions read them and ignored them.
A foreign key needs a parent, or NULL
On the child side, every new or changed value of the foreign key column is looked up in the parent table. INSERT INTO orders VALUES (13, 9) fails, because there is no customer 9:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer`
FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE)
A NULL value is not looked up at all: the row simply has no parent, and that is allowed. So INSERT INTO orders VALUES (13, NULL) works. If every order must belong to a customer, the column also needs NOT NULL. An UPDATE that points an order at customer 9 fails with the same 1452 (Demo: a foreign key needs a parent row (or NULL)).
What happens to the children: ON DELETE and ON UPDATE
On the parent side, the foreign key's referential action decides what a DELETE of a parent row, or an UPDATE of its key, does to the rows that point at it.
| action | parent deleted or key changed | demo result (DELETE customer 1) |
|---|---|---|
RESTRICT (default) | refused with 1451 while child rows exist | error 1451, nothing changes |
NO ACTION | the same as RESTRICT in MariaDB | error 1451 |
CASCADE | child rows are deleted, or get the new key | orders 10, 11 and their 3 items go |
SET NULL | the child column becomes NULL (it must be nullable) | orders 10, 11 stay with customer_id NULL |
SET DEFAULT | not supported | – |
An action is part of the constraint. To change it, drop the constraint and add it again:
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE SET NULL ON UPDATE CASCADE;
With foreign_key_checks = 1, adding the constraint checks every existing row and copies the table, so the client sees "3 rows affected". Demo: ON DELETE SET NULL keeps the orders and Demo: ON DELETE RESTRICT refuses with 1451 start with such an ALTER. Under RESTRICT, the 1451 message names the constraint that still has children:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer`
FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
ON UPDATE CASCADE)
ON UPDATE works the same way for a change of the parent key. fk_orders_customer has ON UPDATE CASCADE, so UPDATE customers SET id = 7 WHERE id = 2 also changes order 12's customer_id to 7 (Demo: ON UPDATE CASCADE carries a new key). Primary keys rarely change, so ON UPDATE matters less than ON DELETE.
Cascades through two levels
A cascaded delete is a delete like any other, so it runs the foreign keys of the table it deletes from. Deleting customer 1 deletes orders 10 and 11 through fk_orders_customer. Deleting those orders deletes their items through fk_items_order. One statement removes 1 customer, 2 orders and 3 items, yet the client is told "1 row affected": rows changed by foreign key actions are not counted (Demo: ON DELETE CASCADE through two levels).
Every level must agree. If fk_items_order is RESTRICT, the cascade reaches orders 10 and 11, finds their items and fails with 1451, this time naming fk_items_order. The statement is undone as a whole, so customer 1 and both orders are still there (Demo: a RESTRICT two levels down blocks the cascade). InnoDB allows at most 15 levels of cascading.
In practice
- Use CASCADE for rows that are part of their parent (an order's items), and RESTRICT for rows that have a life of their own (an order of a customer you must keep for the accounts).
- Use SET NULL only on a nullable column where "no parent" makes sense.
- Name every constraint. The name is in the error, and you need it to drop the constraint.
- Keep
foreign_key_checks = 1. Turning it off for a bulk load lets orphan rows in, and nothing finds them later.
See also ER Model to Tables for where the primary and foreign keys come from in a design, Database Normalization for choosing keys from functional dependencies, MariaDB Triggers and Stored Procedures for rules that a CHECK can't express and for statement rollback, MariaDB Table Elimination for how the optimizer uses a unique key, and Database Index for the indexes the key checks use.
What the page leaves out
One session and autocommit only; what other sessions see and the locks InnoDB takes on parent and child rows are left out. Multi-row statements, INSERT IGNORE (turns some errors into warnings), REPLACE and ON DUPLICATE KEY UPDATE are not shown. Composite foreign keys, a foreign key to the same table, and MATCH FULL / MATCH PARTIAL (parsed but not enforced) are left out. MariaDB has no deferred constraints (DEFERRABLE INITIALLY DEFERRED): every check runs at once, row by row. SET DEFAULT is not supported. Foreign key actions do not fire triggers. Non-transactional engines such as MyISAM and Aria ignore foreign keys.