如何级联从子表到父表的删除？ [英] How to CASCADE a delete from a child table to the parent table?

问题描述

我准备了演示问题的小提琴 。

I prepared 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);

我想要一种在删除子记录时将CASCADE删除到父记录的方法。 br>
例如：

I want a way for the delete to CASCADE to the parent record when a child record is deleted.
For example:

DELETE FROM child WHERE child_name='michael';

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

This should cascade to the parent table and remove the record.

推荐答案

外键仅在另一个方向起作用：级联从父级到子级删除，因此当父级（引用）记录为

Foreign keys only work in the other direction: cascade deletes from parent to child, so when the parent (referenced) record is deleted, any child (referencing) records are also deleted.

如果是1：1关系，则可以创建双向外键关系，其中一侧是最初可延期，并且双方都是级联的。

If it's a 1:1 relationship you can create a bi-directional foreign key relationship, where one side is DEFERRABLE INITIALLY DEFERRED, and both sides are cascade.

否则，您需要将 ON子表上的DELETE ... FOR EACH ROW 触发器，如果​​没有剩余子级，则删除父行。并发 INSERT 可能会导致竞争状况；您需要 SELECT ... FOR UPDATE 父记录，然后检查其他子记录。插入时的外键检查在引用的（父）记录上使用 FOR SHARE 锁定，这样应该可以防止出现任何竞争情况。

Otherwise, you will want an ON DELETE ... FOR EACH ROW trigger on the child table that removes the parent row if there are no remaining children. It's potentially prone to race conditions with concurrent INSERTs; you'll need to SELECT ... FOR UPDATE the parent record, then check for other child records. Foreign key checks on insert take a FOR SHARE lock on the referenced (parent) record, so that should prevent any race condition.

这篇关于如何级联从子表到父表的删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！