如何在外键死锁时删除记录

Question

我有两个表p和u如下：（PostgreSQL 9.3）

CREATE TABLE p
(
  pid integer NOT NULL,
  uid integer,
  CONSTRAINT p_fkey FOREIGN KEY (uid)
      REFERENCES u (uid) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE TABLE u
(
  uid integer NOT NULL,
  pid integer,
  CONSTRAINT u_fkey FOREIGN KEY (pid)
      REFERENCES p (pid) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
);

在p我有：

pid        uid
161556     176266

在我有u

uid        pid
176266     161556

我想做：

DELETE FROM u WHERE uid=176266;
DELETE FROM p WHERE pid=113116;

但我不能。

错误：更新或删除表"u"违反了外键约束 表"p"上的"p_fkey" 详细信息：键（uid）=（176266）仍然从表"p"引用。

我

理解错误，但我不知道我能做些什么来删除。

建议？

Answer 1

您可以在单个语句中删除这两行：

WITH x AS (
   DELETE FROM u WHERE uid = 176266
)
DELETE FROM p WHERE pid = 113116;

这是有效的IMMEDIATE因为在语句末尾检查约束。该语句删除这两行，并在语句末尾满足所有完整性约束。

Answer 2

先尝试更新

update p set uid=0 where uid=176266;
delete from  u where uid=176266;
update u set pid=0 where pid=113116;
delete from p where pid=113116;