How To Delete Record When There Is A Deadlock Of Foreign Keys?

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

Solution 1:

You can delete both rows in a single statement:

WITH x AS (
   DELETEFROM u WHERE uid =176266
)
DELETEFROM 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.

Solution 2:

Try update first

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