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;