sql - 如何将子表的删除级联到父表？

Question

我准备了a fiddle which demonstrates the problem .

CREATE TABLE parent (
   parent_id integer primary key
);

CREATE TABLE child (
   child_name TEXT primary key,
   parent_id integer REFERENCES parent (parent_id) ON DELETE CASCADE
);

INSERT INTO parent VALUES (1);
INSERT INTO child VALUES ('michael',1), ('vanessa', 1);

我想要一种在删除子记录时将删除级联到父记录的方法。
例如:

DELETE FROM child WHERE child_name='michael';

这应该级联到父表并删除记录。

Answer 1

外键只在另一个方向上起作用:从父项到子项的级联删除，因此当父项(引用)记录被删除时，任何子项(引用)记录也被删除。

如果是 1:1 关系，您可以创建双向外键关系，其中一侧是 DEFERRABLE INITIALLY DEFERRED，两侧是级联的。

否则，您需要在子表上使用 ON DELETE ... FOR EACH ROW 触发器，如果​​没有剩余的子行，则删除父行。它可能容易出现并发 INSERT 的竞争条件；您需要 SELECT ... FOR UPDATE 父记录，然后检查其他子记录。插入时的外键检查对引用的(父)记录进行FOR SHARE 锁定，这样可以防止出现任何竞争情况。