Reverse of ON DELETE CASCADE

Question

Suppose I have the following schema:

CREATE TABLE `users` (
  `id` int(10) unsigned auto_increment,
  `historyId` varchar(255),
  PRIMARY KEY  (`id`)
);

CREATE TABLE `histories` (
  `id` int(10) unsigned auto_increment,
  `history` TEXT,
  PRIMARY KEY  (`id`)
);

A User only ever has one History, and the purpose of not having histories point to users is that many other tables (not mentioned in this schema) also have histories.

What's the simplest way to make it so that deleting a User will also delete its History?

Answer 1

You can use trigger like this:

DELIMITER $$
CREATE TRIGGER delete_user_history_on_delete_user
AFTER DELETE ON `users`
FOR EACH ROW
BEGIN
DELETE FROM `histories` WHERE id = old.historyId;
END$$
DELIMITER ;