Constraints

UNIQUE, CHECK, NOT NULL

Constraints stop bad data before it's ever stored.

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(20) UNIQUE NOT NULL,
  price DECIMAL(10,2) CHECK (price >= 0)
);

Cascading deletes

ON DELETE CASCADE automatically deletes child rows when their parent row is removed.

CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE
);

Use cascades carefully

Cascading deletes are convenient, but a single DELETE on a parent can silently wipe out a lot of related data, so use them with care. CHECK constraints are enforced from MySQL 8.0.16.