Constraint management

Add and drop constraints.

ON DELETE and ON UPDATE

With ON DELETE and ON UPDATE you set FOREIGN KEY behavior. Options are CASCADE, SET NULL and RESTRICT.

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE

When the customer is deleted the orders are deleted too.

CASCADE deletes/updates linked rows. SET NULL empties the linked column. RESTRICT blocks the delete. The default is RESTRICT.

Add customer_id FOREIGN KEY REFERENCES customers(id) ON DELETE CASCADE to orders.

Show example solution

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
)

Cascade

FOREIGN KEY (cid) REFERENCES c(id) ON DELETE ____;

Show answer

FOREIGN KEY (cid) REFERENCES c(id) ON DELETE CASCADE;