Reverse ON DELETE CASCADE

Question

Suppose I have the following scheme:

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 story, and the goal of not having a story points to users, so many other tables (not mentioned in this diagram) also have stories.

What is the easiest way to make deleting a user also delete their history?

Answer 1

You can use a trigger as follows:

 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 ; 

Answer 2

I do not think that you can use the foreign key for cascading deletion here, because the data types do not match. You have VARCHAR (255) in one table, and INT (10) in another. (What's up with that?)

I think you will have to use either a trigger or a stored procedure, none of which are completely satisfactory. Triggers are theoretically the safest since client code cannot sneak around them. (Client code may simply not invoke a stored procedure call.) But there are APIs that do not activate MySQL triggers .

MySQL triggers are activated by SQL statements only. They are not triggered by changes to tables made by APIs that do not pass SQL statements to the MySQL server.

Answer 3

If User-history is a 1-1 relationship, you can put the restriction in the users table, not in the "history"