MySQL级联删除带有两个外键

Question

我有三个这样的表：

create table parent (  
key1 not null  
key2 not null  
primary key (key1, key2)  
) engine=innodb;
create table child (  
name  
key1 not null  
key2 not null  
key3 not null  
primary key (key1, key2, key3),  
foreign key (key1) references parent(key1) on delete cascade,  
foreign key (key2) references parent(key2) on delete cascade  
) engine=innodb;  
create table child_denormalization (  
key1 not null  
key2 not null  
key3 not null  
primary key (key1, key2, key3),  
foreign key (key1) references child(key1) on delete cascade,  
foreign key (key2) references child(key2) on delete cascade,  
foreign key (key3) references child(key3) on delete cascade  
) engine=innodb;

现在，父表行由key1和key2唯一标识，但是多个行可能具有相同的key1或key2值，但不能同时具有这两个值。

当我从父表中删除一行时，子表中与被删除行具有相同key1值的所有行都会被删除，即使key2不同。

如果所有外键都与已删除的行匹配，是否有方法只删除级联？

我尝试删除"on delete cascade"，而是在父级删除之前添加一个触发器，以手动删除子级中的行，但在child_key1上仍然出现外键约束错误。

Answer 1

您可以定义复合外键，而不是单独定义它们：

create table parent (  
key1 not null  
key2 not null  
primary key (key1, key2)  
) engine=innodb;
create table child (  
name  
key1 not null  
key2 not null  
key3 not null  
primary key (key1, key2, key3),  
-- composite foreign key instead of individual keys
foreign key (key1, key2) references parent(key1, key2) on delete cascade 
) engine=innodb;