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

In p I have:

pid        uid
161556     176266

in u I have

uid        pid
176266     161556

I want to do:

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

But I cant.

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.

Suggestions?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
avi
  • 1,626
  • 3
  • 27
  • 45

2 Answers2

3

You can delete both rows in a single statement:

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

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks. It's working. Can you explain why this one works? how it avoids the deadlock? – avi Oct 10 '17 at 10:11
  • Is that explanation enough? – Laurenz Albe Oct 10 '17 at 10:23
  • awesome! where did you read about it in PostgreSQL documentation? – avi Oct 10 '17 at 10:56
  • The behaviour of `IMMEDIATE` constraints is explained in the documentation. See for example the documentation for `SET CONSTRAINTS`. The rest was my creativity :^) – Laurenz Albe Oct 10 '17 at 11:38
  • how did you know it set to IMMEDIATE ? I found this to read about https://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred but i'm not sure i understand the difference or moreover why the WITH changes it to after transaction. – avi Oct 10 '17 at 11:46
  • `IMMEDIATE` is the default status for constraints. Your constraints are all `IMMEDIATE`. Another solution would be to change that to `DEFERRABLE`. – Laurenz Albe Oct 10 '17 at 12:25
0

Try update first

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;
Radim Bača
  • 10,646
  • 1
  • 19
  • 33
  • I'm aware of this option but it's messing up with the data which is something I rather not do. For this solution you also require to have uid=0 in u table. – avi Oct 10 '17 at 10:11