Category: postgresql-10

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?