制約違反とロールバック
部屋と時刻の組を一意にした予約表で、必須値・空文字・人数上限・重複を試します。一つのINSERTで二行を追加する場合と、二つのINSERTに分ける場合を比較し、複数行UPDATEの取消しとIGNOREの動作まで確認します。ここではSQLiteの既定の制約違反処理を使います。
説明
表の制約は、不正な値や重複が保存されるのを防ぎます。エラーが出たときは、どの制約に違反したかだけでなく、同じSQL文で先に処理された行や、前のSQL文の変更が残るかも確認する必要があります。
部屋と時刻の組を一意にした予約表で、必須値・空文字・人数上限・重複を試します。一つのINSERTで二行を追加する場合と、二つのINSERTに分ける場合を比較し、複数行UPDATEの取消しとIGNOREの動作まで確認します。ここではSQLiteの既定の制約違反処理を使います。
1部屋と時刻の予約表
emailは必須かつ空文字不可、人数は1～4人とします。
CREATE TABLE reservations(id INTEGER PRIMARY KEY,room TEXT NOT NULL,slot TEXT NOT NULL,email TEXT NOT NULL CHECK(length(email)>0)NULLと空文字を拒否,people INTEGER NOT NULL CHECK(people BETWEEN 1 AND 4)1と4を含む人数範囲,UNIQUE(room,slot)同じ部屋と時刻の重複を拒否) STRICT;
INSERT INTO reservations VALUES(1,'A','10:00','a@example.test',2),(2,'B','10:00','b@example.test',3);
SELECT * FROM reservations ORDER BY id;A室10時に2人、B室10時に3人です。UNIQUE(room,slot)は部屋だけでも時刻だけでもなく、組合せの重複を防ぎます。emailのCHECKは空文字を防ぐもので、メールアドレスの完全な構文検証ではありません。
2必須値へNULLを指定する
連絡先なしの予約を追加します。
INSERT INTO reservations VALUES(3,'A','11:00',NULL必須のemailへ値なしを指定,2);emailのNOT NULL違反です。NULLと空文字は異なり、ここでは値がないこと自体を拒否しています。行3は追加されません。
3空文字を指定する
NULLではなく空の文字列にします。
INSERT INTO reservations VALUES(3,'A','11:00',''長さ0の文字列,2);length(email)>0のCHECK違反です。空文字はNULLではないためNOT NULLだけでは防げません。必要な値の範囲は別の制約で表します。
4上限を超える人数
5人の予約を追加します。
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',5);定員4人を超える人数人数のCHECK違反です。BETWEEN 1 AND 4は1以上4以下なので、5は範囲外です。境界の4は許されます。
5部屋と時刻が重複する予約
IDだけ変えてA室10時へ追加します。
INSERT INTO reservations VALUES(3,'A','10:00'既存予約と同じ組,'c@example.test',2);roomとslotの一意性違反です。主キーidが新しくても、予約対象の組が既存行と同じなので拒否されます。
6一つのINSERTの途中で違反する
一行目は正しく、二行目は5人の予約にします。
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',2)この行単独なら有効,(4,'B','11:00','d@example.test',5)同じ文の二行目が違反;
SELECT id,room,slot,people FROM reservations ORDER BY id;元の1・2だけが残り、正しい一行目の3も保存されません。既定のABORTは失敗したSQL文による変更を取り消します。二行を一つのINSERTとして渡したことが、取消し範囲に関係します。
7二つのINSERTへ分ける
同じ二行を別々のSQL文で追加します。
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',2);先の文は成功
INSERT INTO reservations VALUES(4,'B','11:00','d@example.test',5);後の文だけが失敗
SELECT id,room,slot,people FROM reservations ORDER BY id;今度は1・2・3が残ります。最初のINSERTは成功し、二つ目だけが失敗しています。BEGINでまとめておらず、各文が独立して確定する状態です。エラーが出たからといって、以前の成功まで自動で取り消されるわけではありません。
8複数行のUPDATEで一行が範囲外になる
ID 1を4人、ID 2を9人、他を1人へ変えます。
UPDATE reservations SET people=CASE WHEN id=1 THEN 4上限内への変更 WHEN id=2 THEN 9範囲外への変更 ELSE 1 END;
SELECT id,people変更が戻った人数 FROM reservations ORDER BY id;CHECK違反で文全体が取り消され、人数は2・3・2のままです。どの行が先に処理されたかに依存せず、この既定の処理では失敗したUPDATEの変更は残りません。
9IGNOREで重複行を飛ばす
重複予約と、新しい予約を一つのINSERTで渡します。
INSERT OR IGNORE適用対象の制約違反行を飛ばす INTO reservations VALUES(5,'A','10:00','e@example.test',2),(6,'C','11:00','f@example.test',2);
SELECT changes()保存された行数 AS inserted_rows;
SELECT id,room,slot,people FROM reservations ORDER BY id;ID 5は既存の部屋・時刻と重なるため飛ばされ、ID 6だけが追加されます。エラーは出ず、変更件数は1です。IGNOREを付けると入力した全行が保存されたとは限らないため、採用件数を確認します。
10残った予約の整合性
件数と、部屋・時刻ごとの重複を調べます。
SELECT COUNT(*) AS reservations保存された予約件数,MIN(people) AS minimum,MAX(people) AS maximum FROM reservations;
SELECT room,slot,COUNT(*) AS duplicates FROM reservations GROUP BY room,slot HAVING COUNT(*)>1重複している組だけを取得;予約は4件、人数は最小2・最大3で、重複一覧は0行です。制約が保存を防いだ結果と、IGNOREで飛ばされた入力を区別し、最終的に残った行から状態を確認できます。
まとめ
NOT NULL、CHECK、UNIQUEはそれぞれ別の条件を保存時に検査します。
既定のABORTは失敗した文の変更を戻しますが、以前に成功した別の文とは範囲が異なります。
IGNOREでエラーが消えても、入力した全行が保存されたとは限りません。
操作履歴と解説を振り返れます。
SQLite 3.54.0
CREATE TABLE reservations(id INTEGER PRIMARY KEY,room TEXT NOT NULL,slot TEXT NOT NULL,email TEXT NOT NULL CHECK(length(email)>0),people INTEGER NOT NULL CHECK(people BETWEEN 1 AND 4),UNIQUE(room,slot)) STRICT;
INSERT INTO reservations VALUES(1,'A','10:00','a@example.test',2),(2,'B','10:00','b@example.test',3);
SELECT * FROM reservations ORDER BY id;╭────┬──────┬───────┬────────────────┬────────╮ │ id │ room │ slot │ email │ people │ ╞════╪══════╪═══════╪════════════════╪════════╡ │ 1 │ A │ 10:00 │ a@example.test │ 2 │ │ 2 │ B │ 10:00 │ b@example.test │ 3 │ ╰────┴──────┴───────┴────────────────┴────────╯
INSERT INTO reservations VALUES(3,'A','11:00',NULL,2);Error: NOT NULL constraint failed: reservations.email
INSERT INTO reservations VALUES(3,'A','11:00','',2);Error: CHECK constraint failed: length(email)>0
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',5);Error: CHECK constraint failed: people BETWEEN 1 AND 4
INSERT INTO reservations VALUES(3,'A','10:00','c@example.test',2);Error: UNIQUE constraint failed: reservations.room, reservations.slot
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',2),(4,'B','11:00','d@example.test',5);
SELECT id,room,slot,people FROM reservations ORDER BY id;Error: CHECK constraint failed: people BETWEEN 1 AND 4 ╭────┬──────┬───────┬────────╮ │ id │ room │ slot │ people │ ╞════╪══════╪═══════╪════════╡ │ 1 │ A │ 10:00 │ 2 │ │ 2 │ B │ 10:00 │ 3 │ ╰────┴──────┴───────┴────────╯
INSERT INTO reservations VALUES(3,'A','11:00','c@example.test',2);
INSERT INTO reservations VALUES(4,'B','11:00','d@example.test',5);
SELECT id,room,slot,people FROM reservations ORDER BY id;Error: CHECK constraint failed: people BETWEEN 1 AND 4 ╭────┬──────┬───────┬────────╮ │ id │ room │ slot │ people │ ╞════╪══════╪═══════╪════════╡ │ 1 │ A │ 10:00 │ 2 │ │ 2 │ B │ 10:00 │ 3 │ │ 3 │ A │ 11:00 │ 2 │ ╰────┴──────┴───────┴────────╯
UPDATE reservations SET people=CASE WHEN id=1 THEN 4 WHEN id=2 THEN 9 ELSE 1 END;
SELECT id,people FROM reservations ORDER BY id;Error: CHECK constraint failed: people BETWEEN 1 AND 4 ╭────┬────────╮ │ id │ people │ ╞════╪════════╡ │ 1 │ 2 │ │ 2 │ 3 │ │ 3 │ 2 │ ╰────┴────────╯
INSERT OR IGNORE INTO reservations VALUES(5,'A','10:00','e@example.test',2),(6,'C','11:00','f@example.test',2);
SELECT changes() AS inserted_rows;
SELECT id,room,slot,people FROM reservations ORDER BY id;╭───────────────╮ │ inserted_rows │ ╞═══════════════╡ │ 1 │ ╰───────────────╯ ╭────┬──────┬───────┬────────╮ │ id │ room │ slot │ people │ ╞════╪══════╪═══════╪════════╡ │ 1 │ A │ 10:00 │ 2 │ │ 2 │ B │ 10:00 │ 3 │ │ 3 │ A │ 11:00 │ 2 │ │ 6 │ C │ 11:00 │ 2 │ ╰────┴──────┴───────┴────────╯
SELECT COUNT(*) AS reservations,MIN(people) AS minimum,MAX(people) AS maximum FROM reservations;
SELECT room,slot,COUNT(*) AS duplicates FROM reservations GROUP BY room,slot HAVING COUNT(*)>1;╭──────────────┬─────────┬─────────╮ │ reservations │ minimum │ maximum │ ╞══════════════╪═════════╪═════════╡ │ 4 │ 2 │ 3 │ ╰──────────────┴─────────┴─────────╯
演習完了