Reverse of ON DELETE CASCADE

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?

---

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 ;

---

I don't think you can use a foreign key to cascade deletes here, because the data types don't match. You have VARCHAR(255) in one table, and INT(10) in the other. (What's up with that?)

I think you'll have to use either a trigger or a stored procedure, neither of which is entirely satisfactory. Triggers are theoretically the safest, in that client code can't sneak around them. (Client code could just avoid calling a stored procedure.) But there are APIs that don't activate MySQL triggers.

MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server

---

If User-histories it's a 1-1 relation you can put the constraint in the users table and not in the 'hisories' one