外键死锁时如何删除记录? [英] How to delete record when there is a deadlock of foreign keys?

问题描述

我有两个表 p 和 u 如下:(PostgreSQL 9.3)

I have two tables p and u as following: (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"中引用.

ERROR: update or delete on table "u" violates foreign key constraint "p_fkey" on table "p" DETAIL: Key (uid)=(176266) is still referenced from table "p".

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

I understand the error but I don't know what I can do to make the delete.

建议?

推荐答案

您可以在一条语句中删除两行:

You can delete both rows in a single statement:

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

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

This works because IMMEDIATE constraints is checked at the end of the statement. The statement deletes both rows, and at the end of the statement all integrity constraints are fulfilled.

这篇关于外键死锁时如何删除记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！