A cascading delete automatically removes child-table rows when their related row in a parent table is deleted. It preserves referential integrity by preventing orphaned child rows. section 6.13
Choose the referential action that matches the data relationship:
- Use ON DELETE CASCADE when child rows should disappear with the parent. section 6.13
- Use ON DELETE RESTRICT or NO ACTION when deleting the parent must be prevented. section 6.13
- Use ON DELETE SET NULL when the child rows should remain but their foreign keys should be cleared. section 6.13
Payment methods linked to users
A payment_method table can delete its payment records automatically when the referenced user is deleted: FOREIGN KEY (email) REFERENCES user (email) ON DELETE CASCADE ON UPDATE CASCADE. section 6.13
-- comment: works for MySQL, MariaDB and PostgreSQL -- comment: see the code repo for other RDBMS CREATE TABLE payment_method ( payment_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, card_number CHAR(16) NOT NULL, expiry_date CHAR(4) NOT NULL, email VARCHAR(320) NOT NULL, CONSTRAINT fk_payment_method_user FOREIGN KEY (email) REFERENCES user (email) ON DELETE CASCADE ON UPDATE CASCADE );
View a concrete foreign-key definition containing both cascading delete and cascading update actions. section 6.13
