InnoDB foreign keys enforce referential integrity with ON DELETE / ON UPDATE actions.
Define FK
CREATE TABLE order_lines (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNSIGNED NOT NULL,
sku VARCHAR(32) NOT NULL,
qty INT NOT NULL,
CONSTRAINT fk_lines_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE
) ENGINE=InnoDB;Practice: Run on practice.
CASCADE caution
CASCADE DELETE removes children automatically—confirm product/ops expectations before enabling.
Important interview questions and answers
- Q: FK requires InnoDB?
A: Yes—both parent and child must be InnoDB. - Q: Index on FK column?
A: Required for performance on parent lookups and deletes.
Self-check
- What does ON DELETE CASCADE do?
- Why index order_id?
Pitfall: CASCADE DELETE surprises—confirm with product team.
Interview prep
- CASCADE DELETE?
Deletes child rows when parent deleted.
- InnoDB required?
FK constraints only on InnoDB tables.