mysql foreign key “permissions” on delete

问题:

I'm working on a little support (ticket) system. My tables are tickets and ticket_replies.

Design of tickets table is

id|user_id|title|...

Design of ticket_replies looks like:

id|ticket_id|...

The foreign key I added looks like this:

 ALTER TABLE `ticket_replies` ADDFOREIGN KEY (`ticket_id`)
 REFERENCES `sampleauth`.`tickets`(`id`) ON DELETE CASCADE ON UPDATE CASCADE; 

Now when I delete a "ticket" in the "ticket" table it gets deleted in "ticket_replies" too. The other way this doesn't work, all in all I would like this to work the other way too, so my database has all the time consistency. How to do so?

回答1:

Add this trigger will delete its primary key when you try to delete foreign key

CREATE TRIGGER `ticket_replies_BEFORE_DELETE` BEFORE DELETE ON `ticket_replies` FOR EACH ROW
BEGIN
    DELETE FROM tickets WHERE id = OLD.ticket_id;
END