我有一个包含三个表和外键“on delete”约束的模式,如下所示:
| -> FK (cascade) -> |
Organisation | | Users
| - FK (cascade) Categories -> FK(restrict) -> |
如果我删除了一个组织,我想删除用户和与之相关的类别,但是如果用户引用某个类别,我不能允许删除该类别,除非整个组织被删除。
目前,如果我删除一个组织的类别删除失败,如果有一个用户引用它。这似乎表明mysql在处理users表之前的categories表上的外键约束。
如果用户表中的用户在类别之前被清除,这就不会是问题。
有没有办法告诉mysql处理这些fk约束的顺序,以便按照指定的顺序清除表?
注意:我可以先添加一些代码来显式地清除user表,但是这在代码的设计中是非常复杂的,所以我现在还不想去那里。
还要注意,所需的安全性限制了我可以动态地处理模式,因此更改fk约束或禁用对它们的检查并不是一个真正的选项。我可以把保安换成一次性的。我不想永久放松安全,除非没有其他办法。最好编写上述额外代码
下面是表的create语句,经过编辑可以删除不相关的字段。
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;
1条答案
按热度按时间qhhrdooz1#
正式来说,您无法控制级联操作的顺序。但是,您可能会滥用一些未记录的行为:
对于mysql 5.5,外键是按照它们创建的顺序执行的,因此删除并重新创建
fk_category_org
-约束应该起作用对于MySQL5.6+,外键是按名称的词法顺序执行的,因此重命名
fk_category_org
例如。fk_z_category_org
应该有用这是未记录的,可以随时更改(可能会受到其他因素的影响)。
这就是说,正确的方法来做这件事(和任何其他太复杂的事情)
on cascade
)就是加一个before delete
-触发你的organisation
-先“手动”删除用户,然后再删除类别的表。before delete
-触发器在on cascade
(因此,您可以决定是否要保留这些内容,尽管这可能会产生误导)。目前还不完全清楚这是否是您有意的行为,但目前,用户可以拥有一个属于组织1的类别,而他被分配到组织2。删除组织1仍然会失败。这看起来有点像是你想要阻止你的设计,但如果你想删除工作在这种情况下也,你需要使用触发器,以便能够纳入(或手动删除它在你的应用程序),级联将不会工作,除非你也级联在类别表。