Deleting child record doesn’t delete foreign key reference

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

Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it’s setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

enter image description here

Versus

enter image description here

Go to Source
Author: TugboatCaptain