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