级联删除无法使用1:1关系的SQLite

Question

我正在尝试在两个表Places和People之间建立1:1的关系。一个人有一个家，当这个人被删除时，这个家也应该被删除。其他表也使用Places表，因此Places表中没有引用People表的列。

为了尝试实现这一点，我设置了People表，以便在删除一行时，在指向Places表的外键上进行级联删除。

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
);

然而，当我实际尝试此操作时，Places表中的行仍然存在。有什么办法解决这个问题吗？

---

完全可运行的示例

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;

Answer 1

您在表People中为列HomeId定义的外键的"ON DELETE CASCADE"操作(引用表Places的列Id(意味着：

每当删除表Places中的一行(即父行该关系中的表(表CCD_ 15中保存对已删除行的引用也将被删除。

请参阅演示

在您的情况下，您正在删除表People中的一行，这根本不会影响表Places。