I'm trying to set up a 1:1 relationship between two tables Places and People. A person has a home, and when that person is deleted the home should also be deleted. Other tables also use the Places table, so there is no column in the Places table that refers to the People table.

To try and achieve this, I've set the People table up so that when a row is deleted, there is a cascade delete on the foreign key pointing at the Places table row is also deleted.

CREATE TABLE IF NOT EXISTS "People" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_People" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "HomeId" TEXT NOT NULL,
    CONSTRAINT "FK_People_Places_HomeId" FOREIGN KEY ("HomeId") REFERENCES "Places" ("Id") ON DELETE CASCADE
);

However, when I actually tried this, the row in the Places table still existed. Is there any way to fix this?

Fully runnable example

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS "Places" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Places" PRIMARY KEY,
    "Name" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "People" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_People" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "HomeId" TEXT NOT NULL,
    CONSTRAINT "FK_People_Places_HomeId" FOREIGN KEY ("HomeId") REFERENCES "Places" ("Id") ON DELETE CASCADE
);

DELETE FROM Places;
DELETE FROM People;

INSERT INTO "Places" ("Id", "Name") VALUES ("6f81fa78-2820-48e1-a0a7-b0b71aa38262", "Castle");
INSERT INTO "People" ("Id", "HomeId", "Name") VALUES ("ccb079ce-b477-47cf-adba-9fdac6a41718", "6f81fa78-2820-48e1-a0a7-b0b71aa38262", "Fiona");

-- Should delete both the person and the place, but does not
DELETE FROM "People" WHERE "Id" = "ccb079ce-b477-47cf-adba-9fdac6a41718";

SELECT pl.Name "Place Name",
         po.Name "Person Name"
FROM Places pl
LEFT JOIN People po USING(Name)
UNION ALL
SELECT pl.Name,
         po.Name
FROM People po
LEFT JOIN Places pl USING(Name)
WHERE pl.Name IS NULL;

CodePudding user response：

The "ON DELETE CASCADE" action for the foreign key that you defined in the table People for the column HomeId which references the column Id of the table Places means that:

whenever you delete a row in the table Places (which is the parent table in this relationship) all rows in the table People that hold a reference to the deleted row will also be deleted.

See the demo.

In your case you are deleting a row in the table People and this does not affect at all the table Places.