Why should you care?

Deleting a customer might CASCADE delete their cart lines in dev sandboxes, but prod often RESTRICTs delete until orders archive.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE order_items (
  line_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty        INT NOT NULL,
  CONSTRAINT fk_oi_order
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE
);

What happened?

• When an order row is deleted, InnoDB automatically deletes its order_items lines.
• Without CASCADE you must delete children first manually.

Practice next

1. Create order_items with ON DELETE CASCADE.
2. Insert order + lines.
3. DELETE FROM orders WHERE order_id = X;
4. Switch to ON DELETE RESTRICT and retry delete — error.
5. Compare ON UPDATE CASCADE for rare PK changes.

Remember

CASCADE propagates deletes/updates. RESTRICT blocks parent delete if children exist. Pick policy per business rule.

DataFlow cart cleanup

Dev DB cascades cart lines when test order removed — prod uses soft delete instead.

Outcome: Teams match cascade policy to compliance needs.