一覧DB-05-3 · 更新と制約

外部キーの更新とUPSERT

部署と所属者でキー変更の連動と削除拒否を確認し、削除時のCASCADEとSET NULLを比べます。次に商品在庫を使ってDO UPDATE・DO NOTHING・衝突対象の違いを試し、最後にREPLACEが削除を伴う動作であることを確かめます。

説明

外部キーの参照先を更新・削除するときは、子の行を残すのか、一緒に変更するのかを決めます。また、新規追加の主キーが既存行と重なる場合は、UPSERTで指定した更新へ切り替えられます。

1参照制約付きの部署と所属者

外部キーを有効にし、部署名のキー変更を連動させます。

PRAGMA foreign_keys=ON;
CREATE TABLE teams(code TEXT PRIMARY KEY) STRICT;
CREATE TABLE members(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code)

ON UPDATE CASCADE親キーの更新を子へ連動 ON DELETE RESTRICT参照中の親の削除を拒否

) STRICT;
INSERT INTO teams VALUES('A'),('B');
INSERT INTO members VALUES(1,'A'),(2,'A');
SELECT * FROM members ORDER BY id;

二人とも部署Aを参照します。ON UPDATE CASCADEは親のキー変更を子へ反映し、ON DELETE RESTRICTは参照されている親の削除を拒否します。更新時と削除時で別の動作を指定できます。

2部署のキーを変更する

AをAXへ変更します。

UPDATE teams SET code='AX' WHERE code='A'参照先のキーを変更

SELECT * FROM members連動した子の値 ORDER BY id;

二人のteamもAXへ変わります。子を個別のUPDATEで直したのではなく、外部キーに指定した更新時の動作が適用されています。

3所属者のいる部署を削除する

AXを削除します。

DELETE FROM teams WHERE code='AX'参照されている親を削除

;
SELECT * FROM teams ORDER BY code;

外部キー違反で拒否され、AXとBが残ります。参照中の部署を削除できない設計なので、所属者の移動などを先に行う必要があります。

4削除時に消す子と残す子を用意する

作業行は連動削除、メモは参照だけをNULLへ変更する定義にします。

CREATE TABLE tasks(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code) ON DELETE CASCADE親と一緒に子の行を削除

) STRICT;
CREATE TABLE notes(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code)

ON DELETE SET NULL子の行を残して参照をNULLへ

,body TEXT) STRICT;
INSERT INTO tasks VALUES(10,'AX');
INSERT INTO notes VALUES(20,'AX','部署メモ');

UPDATE members SET team='B'削除を制限する所属者を移動

 WHERE team='AX';
SELECT * FROM members ORDER BY id;

所属者はBへ移りました。AXの削除を妨げるmembersの参照がなくなり、tasksとnotesにはそれぞれ指定した削除時の動作が残ります。SET NULLを使う子の列はNULLを許す必要があります。

5親を削除した後の子を確認する

AXを削除し、作業件数とメモを表示します。

DELETE FROM teams WHERE code='AX'親の削除を再実行

;
SELECT

COUNT(*) AS task_rows連動削除された件数を確認

 FROM tasks;

SELECT * FROM notes参照だけが外れたメモ;

tasksは0件、notesはid 20と本文を保ち、teamだけがNULLです。親の削除後に何を残すかは、子の役割と保存要件に合わせて選びます。

6在庫表へ一行を追加する

SKUを主キー、labelを別の一意な名前にします。

CREATE TABLE inventory(sku TEXT PRIMARY KEY衝突対象にする商品キー,label TEXT NOT NULL UNIQUE別の一意制約,qty INTEGER NOT NULL CHECK(qty>=0)在庫数の範囲

) STRICT;
INSERT INTO inventory VALUES('P1','ノート',5);
SELECT * FROM inventory;

P1の在庫は5です。skuとlabelにはそれぞれ一意性があり、qtyは負数を許しません。次のUPSERTでは、どの一意性違反を処理対象にするかを明示します。

7主キーが重なったら在庫へ加算する

追加予定の3個を、既存の5個へ加えます。

INSERT INTO inventory VALUES('P1','ノート',3) ON CONFLICT(sku)skuの一意性違反を対象にする DO UPDATE SET qty=inventory.qty+excluded.qty既存値と追加予定値を加算 RETURNING *;

P1の在庫が8になります。inventory.qtyは既存値5、excluded.qtyは追加しようとした値3です。skuが一致した行でDO UPDATEへ切り替わり、加算しています。

8重複時に何も変更しない

同じSKUに99個を指定し、DO NOTHINGを使います。

INSERT INTO inventory VALUES('P1','ノート',99) ON CONFLICT(sku) DO NOTHING対象の衝突時は変更しない

;
SELECT

changes()変更件数を確認

 AS changed_rows;
SELECT * FROM inventory;

変更件数は0、在庫は8のままです。DO NOTHINGはこの衝突した行の追加を行わず、既存値も変えません。99を保存したことにはなりません。

9指定していない一意制約への違反でエラーになる

別SKUで同じlabelを使います。

INSERT INTO inventory VALUES('P2','ノート',2)SKUは新しくlabelだけが重複 ON CONFLICT(sku)labelの衝突は対象外 DO UPDATE SET qty=excluded.qty;

labelのUNIQUE違反です。skuは重なっていないので、ON CONFLICT(sku)では処理されません。UPSERTはあらゆる制約違反を吸収する指定ではありません。

10更新へ切り替わっても制約を満たす必要がある

DO UPDATE側で在庫を20減らします。

INSERT INTO inventory VALUES('P1','ノート',1) ON CONFLICT(sku) DO UPDATE SET qty=inventory.qty-20更新後に負数になる式

SELECT * FROM inventory拒否後の在庫;

更新結果が-12になるためCHECK違反となり、在庫は8のままです。衝突を処理できることと、更新後の値が有効であることは別の条件です。

11REPLACEの影響を確認する親子表

商品コードを参照するタグを一件作ります。

CREATE TABLE catalog(sku TEXT PRIMARY KEY,title TEXT) STRICT;
CREATE TABLE tags(id INTEGER PRIMARY KEY,sku TEXT REFERENCES catalog(sku)

ON DELETE CASCADE商品削除でタグも削除

) STRICT;
INSERT INTO catalog VALUES('P1','旧名');

INSERT INTO tags VALUES(1,'P1')既存商品を参照するタグ

;
SELECT * FROM tags;

タグはP1の商品行を参照し、親が削除されたら連動削除される定義です。同じ主キーで商品を入れ直した場合、単なるUPDATEとの差が現れます。

12REPLACEで重複行を削除してから挿入する

同じP1を新名でINSERT OR REPLACEします。

INSERT OR REPLACE既存の衝突行を削除して追加

 INTO catalog VALUES('P1','新名');
SELECT * FROM catalog;
SELECT

COUNT(*) AS tag_rows子のタグが残るか確認 FROM tags;

商品はP1・新名になりましたが、タグは0件です。REPLACEは衝突した既存行を削除してから新しい行を入れるため、削除時のCASCADEが働きました。既存行の値を変えたい場合は、この削除の影響を考慮してUPDATEやUPSERTを選びます。

まとめ

外部キーには更新時・削除時それぞれの連動動作を指定できます。

UPSERTは衝突対象の一意制約を指定し、excludedで追加予定の値を参照します。

REPLACEは削除を伴うため、外部キーの連動削除などUPDATEと異なる影響があります。

操作履歴と解説を振り返れます。

SQLite 3.54.0

PRAGMA foreign_keys=ON;
CREATE TABLE teams(code TEXT PRIMARY KEY) STRICT;
CREATE TABLE members(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code) ON UPDATE CASCADE ON DELETE RESTRICT) STRICT;
INSERT INTO teams VALUES('A'),('B');
INSERT INTO members VALUES(1,'A'),(2,'A');
SELECT * FROM members ORDER BY id;

╭────┬──────╮
│ id │ team │
╞════╪══════╡
│  1 │ A    │
│  2 │ A    │
╰────┴──────╯

UPDATE teams SET code='AX' WHERE code='A';
SELECT * FROM members ORDER BY id;

╭────┬──────╮
│ id │ team │
╞════╪══════╡
│  1 │ AX   │
│  2 │ AX   │
╰────┴──────╯

DELETE FROM teams WHERE code='AX';
SELECT * FROM teams ORDER BY code;

Error: FOREIGN KEY constraint failed
╭──────╮
│ code │
╞══════╡
│ AX   │
│ B    │
╰──────╯

CREATE TABLE tasks(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code) ON DELETE CASCADE) STRICT;
CREATE TABLE notes(id INTEGER PRIMARY KEY,team TEXT REFERENCES teams(code) ON DELETE SET NULL,body TEXT) STRICT;
INSERT INTO tasks VALUES(10,'AX');
INSERT INTO notes VALUES(20,'AX','部署メモ');
UPDATE members SET team='B' WHERE team='AX';
SELECT * FROM members ORDER BY id;

╭────┬──────╮
│ id │ team │
╞════╪══════╡
│  1 │ B    │
│  2 │ B    │
╰────┴──────╯

DELETE FROM teams WHERE code='AX';
SELECT COUNT(*) AS task_rows FROM tasks;
SELECT * FROM notes;

╭───────────╮
│ task_rows │
╞═══════════╡
│         0 │
╰───────────╯
╭────┬──────┬──────────╮
│ id │ team │   body   │
╞════╪══════╪══════════╡
│ 20 │ NULL │ 部署メモ │
╰────┴──────┴──────────╯

CREATE TABLE inventory(sku TEXT PRIMARY KEY,label TEXT NOT NULL UNIQUE,qty INTEGER NOT NULL CHECK(qty>=0)) STRICT;
INSERT INTO inventory VALUES('P1','ノート',5);
SELECT * FROM inventory;

╭─────┬────────┬─────╮
│ sku │ label  │ qty │
╞═════╪════════╪═════╡
│ P1  │ ノート │   5 │
╰─────┴────────┴─────╯

INSERT INTO inventory VALUES('P1','ノート',3) ON CONFLICT(sku) DO UPDATE SET qty=inventory.qty+excluded.qty RETURNING *;

╭─────┬────────┬─────╮
│ sku │ label  │ qty │
╞═════╪════════╪═════╡
│ P1  │ ノート │   8 │
╰─────┴────────┴─────╯

INSERT INTO inventory VALUES('P1','ノート',99) ON CONFLICT(sku) DO NOTHING;
SELECT changes() AS changed_rows;
SELECT * FROM inventory;

╭──────────────╮
│ changed_rows │
╞══════════════╡
│            0 │
╰──────────────╯
╭─────┬────────┬─────╮
│ sku │ label  │ qty │
╞═════╪════════╪═════╡
│ P1  │ ノート │   8 │
╰─────┴────────┴─────╯

INSERT INTO inventory VALUES('P2','ノート',2) ON CONFLICT(sku) DO UPDATE SET qty=excluded.qty;

Error: UNIQUE constraint failed: inventory.label

INSERT INTO inventory VALUES('P1','ノート',1) ON CONFLICT(sku) DO UPDATE SET qty=inventory.qty-20;
SELECT * FROM inventory;

Error: CHECK constraint failed: qty>=0
╭─────┬────────┬─────╮
│ sku │ label  │ qty │
╞═════╪════════╪═════╡
│ P1  │ ノート │   8 │
╰─────┴────────┴─────╯

CREATE TABLE catalog(sku TEXT PRIMARY KEY,title TEXT) STRICT;
CREATE TABLE tags(id INTEGER PRIMARY KEY,sku TEXT REFERENCES catalog(sku) ON DELETE CASCADE) STRICT;
INSERT INTO catalog VALUES('P1','旧名');
INSERT INTO tags VALUES(1,'P1');
SELECT * FROM tags;

╭────┬─────╮
│ id │ sku │
╞════╪═════╡
│  1 │ P1  │
╰────┴─────╯

INSERT OR REPLACE INTO catalog VALUES('P1','新名');
SELECT * FROM catalog;
SELECT COUNT(*) AS tag_rows FROM tags;

╭─────┬───────╮
│ sku │ title │
╞═════╪═══════╡
│ P1  │ 新名  │
╰─────┴───────╯
╭──────────╮
│ tag_rows │
╞══════════╡
│        0 │
╰──────────╯

演習完了