postgresql 如何从子表到父表级联删除？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22471172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to CASCADE a delete from a child table to the parent table?
提问by samol
I prepared a fiddlewhich 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);
I want a way for the delete to CASCADE to the parent record when a child record is deleted.
For example:
我想要一种在删除子记录时将删除到 CASCADE 到父记录的方法。
例如：
DELETE FROM child WHERE child_name='michael';
This should cascade to the parent table and remove the record.
这应该级联到父表并删除记录。
回答by Craig Ringer
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.
外键只在另一个方向起作用：从父级到子级级联删除，因此当父（引用）记录被删除时，任何子（引用）记录也被删除。
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.
如果是 1:1 关系，您可以创建双向外键关系，其中一侧是DEFERRABLE INITIALLY DEFERRED，两侧是级联。
Otherwise, you will want an ON DELETE ... FOR EACH ROWtrigger 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 UPDATEthe parent record, thencheck for other child records. Foreign key checks on insert take a FOR SHARElock on the referenced (parent) record, so that should prevent any race condition.
否则，ON DELETE ... FOR EACH ROW如果没有剩余的子行，您将需要子表上的触发器删除父行。它可能容易出现并发INSERTs 的竞争条件；你需要SELECT ... FOR UPDATE父记录，然后检查其他子记录。插入的外键检查会FOR SHARE锁定引用的（父）记录，这样应该可以防止任何竞争条件。
回答by Erwin Brandstetter
You seem to want to kill the whole family, without regard to remaining children. Run this instead of DELETE FROM child ...:
你似乎想杀了全家，不顾余下的孩子。运行这个而不是DELETE FROM child ...：
DELETE FROM parent p
USING child c
WHERE p.parent_id = c.parent_id
AND c.child_name='michael';
Then everything works with your current design. If you insist on your original DELETEstatement, you needa rule or a trigger. But that would be rather messy.
然后一切都适用于您当前的设计。如果你坚持你的原始DELETE陈述，你需要一个规则或一个触发器。但是那样会比较乱。
回答by Patrick
From your question and sql fiddle, are you sure that you want to delete the parent AND all children if one child is deleted? If so, then use this:
根据您的问题和 sql fiddle，如果删除了一个孩子，您确定要删除父级和所有子级吗？如果是这样，那么使用这个：
CREATE FUNCTION infanticide () RETURNS trigger AS $$
BEGIN
DELETE FROM parent WHERE parent_id = OLD.parent_id;
RETURN NULL;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER dead_kids BEFORE DELETE ON TABLE child
FOR EACH ROW EXECUTE infanticide();
It is imperative that you RETURN NULLfrom the BEFORE DELETEtrigger: you want the delete to fail. The reason is that you delete the parent and that deletion will cascade to the child table (with the proper settings on the parent table). If you then try to delete more children in the same statement you are trying to make the system work on inexistent data and throwing out a parent that is already gone.
您必须RETURN NULL从BEFORE DELETE触发器中删除：您希望删除失败。原因是您删除了父表，该删除操作将级联到子表（在父表上进行适当的设置）。如果您随后尝试在同一语句中删除更多子项，则您是在尝试使系统处理不存在的数据并丢弃已经消失的父项。
