MySQL ON DELETE CASCADE

ON DELETE CASCADE is a foreign key action. When a row in a parent table is deleted, MySQL also deletes child rows that reference it. A single DELETE can therefore remove rows from multiple tables. Use this action only when child rows should not exist without their parent.

The example below uses InnoDB tables with separate names. Run it in a scratch database, and inspect the child rows before deleting a parent.

Define the foreign key

Each building can have multiple rooms. The foreign key on the child table specifies that deleting a building also deletes its rooms:

CREATE TABLE cascade_buildings (
    building_id INT PRIMARY KEY,
    building_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE cascade_rooms (
    room_id INT PRIMARY KEY,
    building_id INT NOT NULL,
    room_name VARCHAR(100) NOT NULL,
    KEY idx_cascade_rooms_building (building_id),
    CONSTRAINT fk_cascade_rooms_building
        FOREIGN KEY (building_id)
        REFERENCES cascade_buildings (building_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

Insert two buildings and three rooms:

INSERT INTO cascade_buildings (building_id, building_name)
VALUES (1, 'North'), (2, 'South');

INSERT INTO cascade_rooms (room_id, building_id, room_name)
VALUES
    (101, 1, 'Lobby'),
    (201, 2, 'Office A'),
    (202, 2, 'Office B');

Check and delete a parent row

Before deleting building 2, inspect the child rows that will be affected:

SELECT room_id, room_name
FROM cascade_rooms
WHERE building_id = 2;

Delete the parent row:

DELETE FROM cascade_buildings
WHERE building_id = 2;

The two rooms associated with building 2 are deleted automatically. Building 1 and its room remain. Cascades continue through other child tables only when those foreign keys also specify a cascading action.

Find foreign keys with cascading deletes

The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table lists each foreign key’s delete rule. This query finds child tables that cascade deletes from cascade_buildings in the current database:

SELECT
    table_name AS child_table,
    constraint_name,
    referenced_table_name AS parent_table
FROM information_schema.referential_constraints
WHERE constraint_schema = DATABASE()
  AND referenced_table_name = 'cascade_buildings'
  AND delete_rule = 'CASCADE'
ORDER BY table_name, constraint_name;

ON DELETE CASCADE requires a storage engine that enforces foreign keys, such as InnoDB or NDB. Without a cascading action, MySQL’s default RESTRICT / NO ACTION behavior rejects deletion while child rows still reference the parent. ON UPDATE CASCADE is a separate action for changes to referenced key values. If child rows should remain but lose their reference, use ON DELETE SET NULL and make the foreign key column nullable.

For other foreign key rules, see the MySQL foreign key tutorial. Consult MySQL’s official foreign key documentation and REFERENTIAL_CONSTRAINTS table reference for engine and metadata details.