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?
Go to Source
Author: leon de vries