0

I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem.

Whenever I delete all child-records referring to a parent, somehow the reference between these two tables persists.

I have a child and parent table:

CREATE TABLE IF NOT EXISTS parent
(
    ID SERIAL NOT NULL CONSTRAINT PK_parent PRIMARY KEY,
    reference VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS child
(
    ID SERIAL NOT NULL CONSTRAINT PK_child PRIMARY KEY,
    parentID INT NOT NULL,
);

ALTER TABLE child
ADD CONSTRAINT FK_child_parent_ID FOREIGN KEY (parentID) REFERENCES parent (ID) MATCH FULL;

I delete all the records from the child table referring to the parent:

DELETE FROM child WHERE parentID = 1;

I select all the records from the child table referring to the parent:

SELECT * FROM child WHERE parentID = 1; // 0 records returned

I try to delete the parent record from the parent table

DELETE FROM parent WHERE ID = 1;

// result
ERROR: update or delete on table "parent" violates foreign key constraint "fk_child_parent_id" on table "child" Detail: Key (id)=(1) is still referenced from table "child".

I know about the ON DELETE option on the FOREIGN KEY constraint, but I just want to do this once...

What do I fail to see?

To be clear:

  • These are separate queries.
  • I've only 1 child table referring to the parent.
  • The child table does not return any records related to the parent.
  • I've checked if I had a duplicate fk constraint, but that is not the case.
  • I am not running the query within a transaction.
3
  • Could you have any row level security rules defined, blocking your user from seeing (and deleting) some of the relevant rows in the child table? ref: postgresql.org/docs/9.5/ddl-rowsecurity.html – David Spillett Aug 25 '20 at 16:02
  • select * from child where id=1 should return some value referenced in parent table. It should work. dbfiddle.uk/… – McNets Aug 25 '20 at 16:03
  • 4
    I guess by trying to reduce your example to a minimum you accidentally removed some relevant parts from it that actually cause the issue. – mustaccio Aug 25 '20 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.