我有三张这样的table:
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”,而是在父级上的delete之前添加一个触发器来手动删除子级中的行,但是在子级上仍然得到一个外键约束错误。
1条答案
按热度按时间w8f9ii691#
您可以定义复合外键,而不是单独定义它们: