SQL-如何删除互相引用的两个实体

本文介绍了SQL-如何删除互相引用的两个实体的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

所以我有两个互相引用的实体，父，孩子。

So I have two entities referencing each other, parent, child.

child ，但在还有一个父母引用它。

child must be deleted if parent is deleted, but cannot be deleted while there's still a parent referencing it.

这些是我得到的两个约束：

These are the two constraints I've been given:

ALTER TABLE public.parent
  ADD CONSTRAINT parent__child_id__fk
    FOREIGN KEY (child_id) REFERENCES child(id)
    ON DELETE CASCADE
;

ALTER TABLE public.child
  ADD CONSTRAINT child__parent_code__id__fk
    FOREIGN KEY (parent_code, id) REFERENCES parent(code, child_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
    DEFERRABLE INITIALLY DEFERRED
;

我现在想删除父母（以及相应的 child ）...

I now want to delete a parent (and the corresponding child) ...

SQL Error [23503]:
ERROR: update or delete on table "parent" violates foreign key constraint
"child__parent_code__id__fk" on table "child"

    Detail: Key (code, child_id)=(A0B7EBF6-3_DELETE_ME, 10)
            is still referenced from table "child".

Whoop-dee-doo ...

是的，我尝试删除的流血条目引用了它。

Yes, it's referenced by the bloody entry I'm trying to delete...

（我知道这是因为 parent.code ）

如果设置了 child 到在DELETE CASCADE 上的键，但这似乎并不是那个家伙my着我的脖子，这就是如果您删除父级也删除其子级，如果您删除具有以下内容的子级：父母，不要。

Looks like I CAN delete the entry if I set the child's fk to ON DELETE CASCADE, but that doesn't seem to be what the guy breathing down my neck wants, which is "if you delete a parent delete its child, too, if you delete a child that has a parent, DON'T".

如何实现？

`推荐答案`

使用CTE从一条语句中的两个表中删除：

Delete from both tables in one statement using a CTE:

WITH x AS (
    DELETE FROM parent
    WHERE ...
    RETURNING id
)
DELETE FROM child
WHERE ...;

                        这篇关于SQL-如何删除互相引用的两个实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！