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.

A new row goes through five checks from left to right: NOT NULL, CHECK, PRIMARY KEY, UNIQUE and FOREIGN KEY. Under each check is the error it gives when it fails: 1048 Column cannot be null, 4025 CONSTRAINT failed, 1062 Duplicate entry for key PRIMARY, 1062 Duplicate entry for key email, 1452 Cannot add or update a child row. A row that passes all five is written. On the right, a DELETE of a parent row that still has child rows under RESTRICT gives 1451 Cannot delete or update a parent row.
Each kind of constraint has its own error. The first check that fails stops the statement.

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

constraintthe ruleerror when it fails
NOT NULLthe column must have a value1048 Column 'name' cannot be null
CHECKan expression on the row must not be false4025 CONSTRAINT `chk_qty` failed for `shop`.`order_items`
PRIMARY KEYunique and NOT NULL; one per table1062 Duplicate entry '1' for key 'PRIMARY'
UNIQUEno two rows share a value (NULLs may repeat)1062 Duplicate entry 'ana@x.com' for key 'email'
FOREIGN KEYa child value must exist in the parent1452 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.

actionparent deleted or key changeddemo result (DELETE customer 1)
RESTRICT (default)refused with 1451 while child rows existerror 1451, nothing changes
NO ACTIONthe same as RESTRICT in MariaDBerror 1451
CASCADEchild rows are deleted, or get the new keyorders 10, 11 and their 3 items go
SET NULLthe child column becomes NULL (it must be nullable)orders 10, 11 stay with customer_id NULL
SET DEFAULTnot supported–
Three columns, one per action, for DELETE FROM customers WHERE id = 1. CASCADE: customer 1, orders 10 and 11 and items (10,1), (10,2), (11,1) are crossed out; left: 1 customer, 1 order, 1 item. SET NULL: customer 1 is gone, orders 10 and 11 stay with customer_id NULL, all 4 items stay. RESTRICT: error 1451, all rows stay.
One DELETE, three actions: the children go, lose their parent, or stop the delete.

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).

Left: DELETE customer 1 goes down a chain. customers row 1 by CASCADE to orders 10 and 11, by CASCADE to items (10,1), (10,2), (11,1). Result: Query OK, 1 row affected. Right: the same chain, but fk_items_order is RESTRICT. The cascade reaches orders 10 and 11, their items block it, ERROR 1451 names fk_items_order and the whole statement is undone, so customer 1 and both orders stay.
A cascade goes down level by level, and a RESTRICT anywhere on the way stops the whole statement.

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.

On the mind map

Open the Databases & Storage mind mapDatabases& StorageSchema and storage: how rows land on diskSchema and storageIndexes: find rows without a full scanIndexesRunning a query: parse, plan, executeRunning a queryCost-based optimizer: estimate rows, pick cheapest planCost-based optimizerQuery rewrites: same answer, simpler queryQuery rewritesTransactions: ACID: isolation and recoveryTransactionsApps and connections: the client side of the databaseApps and connectionsMongoDB: documents, replica sets, shardsMongoDBRelational, document, graphRelational, document, graphone résumé as tables, JSON and a graphNormalizationNormalizationeach fact once: no partial, transitive depsClosure, Keys, BCNFClosure, Keys, BCNFBCNF can lose an FD; 3NF never has toRow vs column storeRow vs column storerows for lookups, columns for SUMStar schema & OLAP cubeStar schema & OLAP cuberoll-up = GROUP BY; slice = WHERELSM vs B+ treeLSM vs B+ treeLSM: fast writes; B+ tree: fast readsSQLite internalsSQLite internalsone file of pages, a b-tree per tableER model to tablesER model to tablesentities, relations, keys become tablesIntegrity constraints & FKs1062 dup, 1452 no parent, 1451 has childrenOpen the Databases & Storage mind mapDatabases & Storagehow rows land on diskSchema and storageRelational, document, graphRelational, document, graphone résumé as tables, JSON and a graphNormalizationNormalizationeach fact once: no partial, transitive depsClosure, Keys, BCNFClosure, Keys, BCNFBCNF can lose an FD; 3NF never has toRow vs column storeRow vs column storerows for lookups, columns for SUMStar schema & OLAP cubeStar schema & OLAP cuberoll-up = GROUP BY; slice = WHERELSM vs B+ treeLSM vs B+ treeLSM: fast writes; B+ tree: fast readsSQLite internalsSQLite internalsone file of pages, a b-tree per tableER model to tablesER model to tablesentities, relations, keys become tablesIntegrity constraints & FKs1062 dup, 1452 no parent, 1451 has children
This page on the Databases & Storage mind map · 55 pages in 8 branches · open it to see the whole map and test yourself in recall mode.