具有外键约束的删除顺序， [英] Order of deletion with foreign key constraints,

问题描述

我有一个包含三个表和外键删除时"约束的架构，如下所示:

I have a schema with three tables and foreign key 'On Delete' constraints as below:

               |      ->         FK (cascade)        ->       |
Organisation   |                                              |  Users
               | - FK (cascade) Categories -> FK(restrict) -> |

如果删除组织，则要删除用户及其相关类别，但是如果用户引用了该类别，则不允许删除该类别，除非要删除整个组织.

If I delete an organisation I want to delete the users and the categories related to it, but I can't allow a category to be deleted if a user refers to it except in the case where the whole organisation is being deleted.

目前，如果我删除组织，则如果有用户引用该组织，则类别删除将失败.这似乎表明MySQl正在处理Users表之前的Categories表上的外键约束.

At present if I delete an organisation the category deletion fails if there's a user referring to it. This seems to indicate that MySQl is processing the foreign key constraints on the Categories table before the Users table.

如果在类别之前清除了用户表中的用户"，这将不是问题.

This wouldn't be a problem if the Users in the user table were cleared before the Categories.

是否可以告诉MySQl处理这些FK约束的顺序如何，以便按指定顺序清除表?

注意:我可以添加一些代码来首先显式清除用户表，但这只是在代码设计之内，所以我还不想去那里.

Note: I could add some code to explicitly clear the user table first, but that's fiddly within the design of the code so I don't want to go there yet.

还请注意，所需的安全性限制了我对即时模式的处理能力，因此更改FK约束或禁用对它们的检查并不是真正的选择.我可以更改安全性以进行一次更改.除非没有其他方法，否则我不想永久放宽安全性.最好编写如上所述的额外代码

Note also that the required security limits what I can do with the schema on-the-fly, so changing the FK constraints or disabling checking of them is not really an option. I can change the security to make a one-time change. I don't want to loosen security permanently unless there's no other way. Writing extra code as above is preferred

这是表的Create语句，已对其进行了编辑以删除不相关的字段.

Here are the Create statements for the tables, edited to remove unrelated fields.

CREATE TABLE `organisation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orgGUID` varchar(36) NOT NULL,
  `archivedFlag` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `orgName` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `org_guid_UNIQUE` (`orgGUID`)
) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userGUID` varchar(36) NOT NULL,
  `name` varchar(45) NOT NULL,
  `orgGUID` varchar(36) NOT NULL,
  `userType` smallint(6) DEFAULT NULL,
  `PwHash` varchar(255) DEFAULT NULL,
  `ethnicityGUID` varchar(36) DEFAULT NULL ,
  `genderGUID` varchar(36) DEFAULT NULL ,
  `yearGroupGUID` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `userGUID_UNIQUE` (`userGUID`),
  KEY `fk_user_org_idx` (`orgGUID`),
  KEY `fk_ethnicity_category_idx` (`ethnicityGUID`),
  KEY `fk_gender_category_idx` (`genderGUID`),
  CONSTRAINT `fk_ethnicity_category` FOREIGN KEY (`ethnicityGUID`) REFERENCES `categories` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_gender_category` FOREIGN KEY (`genderGUID`) REFERENCES `categories` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_org` FOREIGN KEY (`orgGUID`) REFERENCES `organisation` (`orgGUID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;

CREATE TABLE `categories` (
  `id` varchar(36) NOT NULL,
  `orgGUID` varchar(36) NOT NULL,
  `categoryType` varchar(20) NOT NULL,
  `category` varchar(45) NOT NULL,
  `priority` int(11) NOT NULL,
  `analysisCode` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_category_org_idx` (`orgGUID`),
  CONSTRAINT `fk_category_org` FOREIGN KEY (`orgGUID`) REFERENCES `organisation` (`orgGUID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

推荐答案

正式地，您无法控制级联操作的顺序.但是，您可能会滥用某些未记录的行为:

Officially, you have no control over the order of the cascaded operations. You may be able to abuse some undocumented behaviour however:

• 对于MySQL 5.5，外键按其创建顺序执行，因此删除并重新创建fk_category_org -constraint应该有效
• 对于MySQL 5.6+，外键按其名称的词法顺序执行，因此将fk_category_org重命名为例如. fk_z_category_org应该可以工作

• for MySQL 5.5, the foreign keys are executed in the order they got created, so dropping and recreating the fk_category_org-constraint should work
• for MySQL 5.6+, the foreign keys are executed in the lexical order of their names, so renaming fk_category_org to e.g. fk_z_category_org should work

这是无证的，可以随时更改(并且可能会受到其他因素的影响).

This is undocumented and can change anytime (and might be influenced by other factors).

话虽这么说，执行此操作的正确方法(以及对on cascade而言太复杂的任何其他操作)将是添加before delete-before delete触发器在on cascade之前执行(因此您可以决定是否保留这些触发器，尽管这可能会引起误解).

That being said, the proper way to do this (and anything else too complicated for on cascade) would be to add a before delete-trigger on your organisation-table that "manually" deletes the users first and then the categories afterwards. before delete-triggers are executed before on cascade (so you can decide if you want to keep those or not, although it would probably be misleading).

尚不清楚这是否是您的预期行为，但是当前，用户在分配给组织2时可以拥有属于组织1的类别，然后删除组织1仍然会失败.看起来好像是您要通过设计防止的事情，但是如果您也希望删除在这种情况下也起作用，则 需要使用触发器来合并该触发器(或在您的应用程序中手动将其删除)，除非您还在类别表中级联，否则级联将不起作用.

It is not entirely clear if that is your intented behaviour, but currently, a user can have a category that belongs to organization 1 while he is assigned to organization 2. Deleting organization 1 would then still fail. It looks a bit as if that is what you want to prevent by your design, but if you want the deletion to work in this case too, you need to use the trigger to be able to incorporate that (or manually delete it in your application), cascading will not work unless you also cascade in the category table.

这篇关于具有外键约束的删除顺序，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！