級聯洗掉無法使用1:1關系的SQLite-有解無憂

我正在嘗試在兩個表Places和People. 一個人有一個家，當那個人被洗掉時，這個家也應該被洗掉。其他表也使用該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;

uj5u.com熱心網友回復：

您在表中為參考表列的列定義的"ON DELETE CASCADE"外鍵操作意味著：PeopleHomeIdIdPlaces

每當您洗掉表中的一行Places（即此關系中的父表）時，該表People中包含對已洗掉行的參考的所有行也將被洗掉。

請參閱演示。

在您的情況下，您正在洗掉表中的一行，People這根本不影響表Places。

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/451664.html

標籤：sqlite 级联删除

上一篇：嘗試使我的資料框數字化時遇到問題

下一篇：按日期時間劃分的Sqlite3視窗框架不起作用