mysql外键“权限”删除 [英] mysql foreign key "permissions" on delete

问题描述

我正在做一些支持（票证）系统。我的表是门票和ticket_replies。

设计门票表是

  id | user_id | title | ... 
  

ticket_replies的设计如下所示：

  id | ticket_id | ... 
  

我添加的外键如下所示：

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

现在当我删除ticket表中的ticket时，它将被删除在ticket_replies太。另一种方式是行不通的，总而言之，我希望这样做也是另一种方式，所以我的数据库一直保持一致。如何做到这一点？

解决方案

添加此触发器将删除其主键当您尝试删除外键

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

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?

解决方案

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

这篇关于mysql外键“权限”删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！