Foreign Keys

Foreign Keys

Foreign Keys and Referential Actions

A foreign key requires a child value to exist in the parent: orders.customer_id must be a customers.id. The default action, NO ACTION (the same as RESTRICT), rejects a parent change while children exist; CASCADE deletes or updates the children, and SET NULL clears their column. The listing tests the default, then makes order lines die with their order and lets a subcategory outlive its parent:

Foreign key violations, then CASCADE and SET NULLSQL

INSERT INTO orders (customer_id, ordered_at) VALUES (99, NOW());
DELETE FROM customers WHERE id = 1;
ALTER TABLE order_items
  DROP FOREIGN KEY order_items_ibfk_1,
  ADD CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (id)
      ON DELETE CASCADE;
ALTER TABLE categories
  DROP FOREIGN KEY categories_ibfk_1,
  ADD CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories (id)
      ON DELETE SET NULL ON UPDATE CASCADE;
INSERT INTO categories (name, parent_id) VALUES ('Gifts', NULL), ('Posters', 6);
DELETE FROM orders WHERE id = 5;
DELETE FROM categories WHERE name = 'Gifts';
SELECT (SELECT COUNT(*) FROM order_items WHERE order_id = 5) AS items_left,
       (SELECT parent_id FROM categories WHERE name = 'Posters') AS posters_parent;

Output

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES
    `customers` (`id`))
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  (`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES
    `customers` (`id`))
+------------+----------------+
| items_left | posters_parent |
+------------+----------------+
|          0 |           NULL |
+------------+----------------+

Name keys you may change; DROP FOREIGN KEY needs the name. Types and signs must match (error 3780), and the parent must be a primary or unique key (error 6125). SET SESSION foreign_key_checks = 0 skips checks for bulk loads, and nothing re-validates afterward: an order for customer 99 went in and stayed.

MENU