SQL の INSERT・UPDATE・DELETE と制約を実際に壊して学ぶ

シリーズ：SQL 講座 2/6（初級）。 SELECT でデータを読めるようになり（第1回）、次は他の人も使っているデータベースのデータを自分で変更しなければならない人向けです。読み終えると、行の追加・更新・削除ができ、おかしなデータは制約に止めてもらい、「WHERE なしの UPDATE」事故をただの出来事で終わらせる習慣を1つ身につけられます。

読むだけなら安全ですが、書き込みは別物です。本番 DB で WHERE を付け忘れた UPDATE が全会員のステータスを書き換えた話、退会済みの顧客を指す受注データが何百件も残っていた話は、どこの現場にも1つはあります。この記事はそうした事故を、捨ててよいデータベースでわざと再現し、SQLite が出すメッセージをそのまま見せます。出力はすべて Apple Silicon Mac（M1 Pro）の sqlite3 3.51.0 シェルで実際に実行した結果です。

準備：ルール入りのクラス名簿

クラス（classes）と、そのクラスの生徒（students）からなる小さな学校データベースを使います。テーブル定義には制約、つまりデータベースが変更のたびに検査するルールが入っています。school.sql として保存し、sqlite3 school.db < school.sql で実行します。

PRAGMA foreign_keys = ON;
CREATE TABLE classes (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE students (
  id       INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  name     TEXT NOT NULL,
  score    INTEGER CONSTRAINT score_range CHECK (score BETWEEN 0 AND 100),
  class_id INTEGER REFERENCES classes(id)
);
INSERT INTO classes (name) VALUES ('1-A'), ('1-B');
INSERT INTO students (username, name, score, class_id) VALUES
  ('mina01', 'Mina', 82, 1),
  ('jun02',  'Jun',  67, 1),
  ('sora03', 'Sora', 91, 2),
  ('leo04',  'Leo',  55, 2);

それぞれのルールの意味：

• PRIMARY KEY（主キー）：行を識別します。SQLite の INTEGER PRIMARY KEY は特別で、値を省くと次の番号を自動で振ります。
• NOT NULL：値が必ず必要。
• UNIQUE：同じ値の行が2つあってはいけない（ユーザー名の重複禁止）。
• CHECK：自分で書く任意の条件。CONSTRAINT score_range のように名前を付けるとエラーメッセージが読みやすくなります。
• REFERENCES（外部キー）：class_id は classes に実在する行を指していなければならない。

その後 sqlite3 school.db で対話的に開き、.headers on と .mode table を入力します。

INSERT と、制約が拒否するときのメッセージ

良い INSERT は列名を書きます。後で誰かが列を追加しても動き続けるからです。

INSERT INTO students (username, name, score, class_id) VALUES ('hana05', 'Hana', 74, 1);
SELECT last_insert_rowid();     -- 5：いま SQLite が振った id

次に、制約1つにつき1つずつ、間違った INSERT を4つ。

INSERT INTO students (username, score, class_id) VALUES ('noname', 50, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('mina01', 'Mina Two', 70, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('kai06', 'Kai', 105, 1);
INSERT INTO students (username, name, score, class_id) VALUES ('kai06', 'Kai', NULL, 1);

Runtime error near line 6: NOT NULL constraint failed: students.name (19)
Runtime error near line 7: UNIQUE constraint failed: students.username (19)
Runtime error near line 8: CHECK constraint failed: score_range (19)

（メッセージの行番号は私たちのスクリプトファイル基準です。）3回拒否され、4つ目は成功しました。カイは点数が空のまま入っています。CHECK は条件が偽のときだけ失敗し、NULL BETWEEN 0 AND 100 は「不明」なので通ります。第1回の NULL の論理そのままです。点数が必須なら NOT NULL も一緒に付けます。(19) は「制約違反」を表す SQLite のコードで、アプリ側では例外（Python なら sqlite3.IntegrityError）として受け取ります。

FOREIGN KEY：有効にするまでは無効

新しい sqlite3 school.db セッションで、存在しない99組に生徒を入れてみました。

INSERT INTO students (username, name, score, class_id) VALUES ('ghost', 'Ghost', 60, 99);
PRAGMA foreign_keys;

+--------------+
| foreign_keys |
+--------------+
| 0            |
+--------------+

エラーは出ません。セットアップスクリプトの1行目に PRAGMA foreign_keys = ON があったのに、入ってしまいました。SQLite の外部キー検査は既定で無効、しかも接続ごとの設定で、ファイルには保存されません。シェルのセッションでも Python スクリプトでもスマホアプリでも、接続のたびに有効にし直す必要があります。無効の間に入り込んだ行はこうして探します。

PRAGMA foreign_key_check;

+----------+-------+---------+------+
|  table   | rowid | parent  | fkid |
+----------+-------+---------+------+
| students | 7     | classes | 0    |
+----------+-------+---------+------+

有効にすると、同じミスが両方向で止まります。

PRAGMA foreign_keys = ON;
INSERT INTO students (username, name, score, class_id) VALUES ('ghost2', 'Ghost2', 60, 99);
DELETE FROM classes WHERE id = 2;                 -- 1-B にはまだ生徒がいる
UPDATE students SET class_id = 3 WHERE username = 'leo04';

Runtime error near line 6: FOREIGN KEY constraint failed (19)
Runtime error near line 7: FOREIGN KEY constraint failed (19)
Runtime error near line 10: FOREIGN KEY constraint failed (19)

このメッセージはほかと違い、どの列・どのテーブルが原因かを教えてくれません。外部キーが複数あるテーブルでは自分で突き止める必要があり、PRAGMA foreign_key_list(students) で一覧を見られます。幽霊の生徒は DELETE FROM students WHERE username = 'ghost'; で消しました（changes() は1）。

WHERE なし UPDATE 事故と、トランザクションという安全網

UPDATE は WHERE に合うすべての行の列を変え、DELETE は合うすべての行を消します。WHERE を省くと「合うすべての行」が「すべての行」になります。1人の点数だけをリセットするつもりが、1行早く Enter を押してしまった場面です。

BEGIN;
UPDATE students SET score = 0;
SELECT id, name, score FROM students;

changes: 6   total_changes: 6
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1  | Mina | 0     |
| 2  | Jun  | 0     |
| 3  | Sora | 0     |
| 4  | Leo  | 0     |
| 5  | Hana | 0     |
| 6  | Kai  | 0     |
+----+------+-------+

先に BEGIN を打っていたので、まだ何も確定していません。トランザクションは複数の文をまとめ、COMMIT すれば一度に反映し、ROLLBACK すれば何も反映しません。

ROLLBACK;
SELECT id, name, score FROM students;

+----+------+-------+
| id | name | score |
+----+------+-------+
| 1  | Mina | 82    |
| 2  | Jun  | 67    |
| 3  | Sora | 91    |
| 4  | Leo  | 55    |
| 5  | Hana | 74    |
| 6  | Kai  |       |
+----+------+-------+

全員の点数が戻りました。BEGIN がなければ文ごとにその場でコミットされ（自動コミット）、戻す手段はバックアップだけです。大事な DB を手で直すときの習慣：

1. 同じ WHERE でまず SELECT し、件数を数える。
2. BEGIN;
3. UPDATE か DELETE を実行し、changes（または SELECT changes();）を1の件数と比べる。
4. 一致すれば COMMIT;、違えば ROLLBACK;。

もう1つ罠があります。新しくコピーした DB で試すと、トランザクションの中でエラーが起きても、トランザクション全体はロールバックされず、失敗した文だけが取り消されます。

BEGIN;
INSERT INTO students (username, name, score, class_id) VALUES ('rin07', 'Rin', 88, 2);
INSERT INTO students (username, name, score, class_id) VALUES ('mina01', 'Mina again', 70, 1);
COMMIT;

Runtime error near line 5: UNIQUE constraint failed: students.username (19)

実行後、rin07 はテーブルに入っていました。成功した半分を COMMIT が保存してしまったのです。同じスクリプトの1行目に .bail on を入れて流すと、シェルはエラーの時点で終了コード1で止まり、COMMIT は実行されず rin07 は保存されませんでした（件数0）。アプリのコードなら「例外を捕まえて rollback を呼ぶ」が同じ役目です。

UPDATE・DELETE を正しく：changes と RETURNING

ここからは上の習慣どおり、意図した変更を3回行います。シェルで .changes on にすると文ごとに変更行数が出ます（SELECT changes(); はどこでも同じ値を返します）。

まず BEGIN … COMMIT の中で UPDATE students SET score = score + 5 WHERE class_id = 1 を実行すると changes: 4 でしたが、カイの点数は空のままでした。NULL + 5 は NULL だからです。「4行変更」は「4行に数値が入った」という意味ではありません。

続いて2つ、RETURNING（SQLite 3.35+）付きで。文が触れた行をそのまま見せてくれます。

UPDATE students SET score = 100 WHERE name = 'Sora' RETURNING id, name, score;
DELETE FROM students WHERE id = 6 RETURNING *;

+----+------+-------+
| id | name | score |
+----+------+-------+
| 3  | Sora | 100   |
+----+------+-------+
changes: 1   total_changes: 11
+----+----------+------+-------+----------+
| id | username | name | score | class_id |
+----+----------+------+-------+----------+
| 6  | kai06    | Kai  |       | 1        |
+----+----------+------+-------+----------+
changes: 1   total_changes: 12

キーにまつわるよくある間違い：何が起き、どう直すか

INSERT OR REPLACE は UPDATE ではありません。 ミナの名前だけを変えたかった場面です。

INSERT OR REPLACE INTO students (username, name) VALUES ('mina01', 'Mina K.');
SELECT id, username, name, score, class_id FROM students WHERE username LIKE 'mina%';

+----+----------+---------+-------+----------+
| id | username |  name   | score | class_id |
+----+----------+---------+-------+----------+
| 6  | mina01   | Mina K. |       |          |
+----+----------+---------+-------+----------+

元のミナは id 1、点数87、1組でした。REPLACE はその行を削除して新しい行を入れるので、id が変わり、指定しなかった列は NULL になりました。「生徒1番」を保存していたほかのデータは、もう何も指していません。直し方は、既存の行をその場で更新するアップサートです。

INSERT INTO students (username, name, score, class_id) VALUES ('jun02', 'Jun', 90, 1)
  ON CONFLICT (username) DO UPDATE SET score = excluded.score;

ジュンは id 2 と1組のまま、点数だけが90になりました（excluded は「挿入しようとした行」）。3つ目の選択肢 INSERT OR IGNORE は、衝突すると黙って何もしません（changes: 0）。

id は再利用されます。 上のミナの新しい id を見てください。6は、直前に削除したカイの id でした。確かめるため、いちばん大きい id（6）の行をもう一度消して新しい生徒を入れると、新しい行がまた id 6 を受け取りました。AUTOINCREMENT キーワードがなければ SQLite は「最大 id + 1」を使うので、ほかのテーブルやログが古い6番を覚えていれば、今は別人を指すことになります。id が決して戻ってきてはいけないなら AUTOINCREMENT を付けます。

TEXT の主キーは NULL になれます。 互換性のために残されている古い SQLite のバグで、整数でない主キーは NULL を、しかも何度でも受け入れます。

CREATE TABLE tags (code TEXT PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (NULL, 'first'), (NULL, 'second');   -- 両方入る
CREATE TABLE tags2 (code TEXT PRIMARY KEY NOT NULL, label TEXT);
INSERT INTO tags2 VALUES (NULL, 'first');

Runtime error near line 16: NOT NULL constraint failed: tags2.code (19)

整数でないキーには NOT NULL を明示します（第1回の STRICT テーブルなら自動で強制されます）。

判断表：どのルールに何を使うか

実測：DELETE を3,600倍遅くした外部キー

制約はタダではなく、中でも1つはほぼ全員を驚かせます。外部キーが有効な状態で親の行を消すと、SQLite はその行を指す子の行が残っていないかを確かめる必要があります。子の列（students.class_id）にインデックスがないと、この確認は子テーブル全体を読み、それを消す親の行ごとに繰り返します。

クラス2,000件と生徒200,000件を作り（生徒のクラス番号は固定の式で1〜1,000組に均等に分布、乱数なし）、生徒のいない1,001〜1,100組の100件を削除しました。繰り返せるよう各削除は BEGIN … ROLLBACK の中で行い、Python の sqlite3 モジュール（SQLite 3.51.0）で5回の中央値を M1 Pro で測りました。

con.execute("PRAGMA foreign_keys = ON")
con.execute("BEGIN")
t = time.perf_counter()
con.execute("DELETE FROM classes WHERE id BETWEEN 1001 AND 1100")   # 100行
elapsed = time.perf_counter() - t
con.execute("ROLLBACK")

外部キーを有効にするのは正しい選択ですが、子の列のインデックスも同時に作りましょう。CREATE INDEX 1行で、削除は検査なしと同じ速さ、約3,600倍速く戻りました。SQLite はこのインデックスを自動では作りません（MySQL の InnoDB は作る。ここでは未実行）。インデックスは第4回できちんと扱います。

正直な限界とバージョンメモ

• 実行したのは SQLite 3.51.0 だけです（シェルと Python 3.9 の sqlite3 モジュール、どちらも 3.51.0）。SQLite のリリースノートでは、アップサート（ON CONFLICT … DO UPDATE）が 3.24.0、RETURNING が 3.35.0、外部キー対応が 3.6.19 からです。古い Android にはもっと古い SQLite が入っています。
• INSERT OR REPLACE、OR IGNORE、NULL になれる TEXT 主キーは SQLite の動作です。MySQL には REPLACE INTO（やはり削除してから挿入）と INSERT … ON DUPLICATE KEY UPDATE、PostgreSQL には SQLite に近い ON CONFLICT があります（ここでは未実行）。
• PostgreSQL では SQLite と違い、トランザクション内でエラーが起きるとロールバックするまでトランザクション全体が中断状態になります（ここでは未実行）。
• 計測はノート PC 1台、OS キャッシュに載ったデータでの値です。3,600倍という比は子テーブルの大きさ（ここでは20万行）で変わり、大きいほど開きます。

練習問題

事故の場面の直前の名簿（生徒6人、外部キー有効）から始めます。

1. 生徒 yuna08（Yuna、77点）を1-B 組に追加し、1-B 組の全員に3点ずつ加点してください。ただし100点を超えないように。
2. 1-B 組を1-A 組に統合することになりました。外部キーが有効だと DELETE FROM classes WHERE name = '1-B'; は失敗します。安全に統合してください。

解答

1.

INSERT INTO students (username, name, score, class_id) VALUES ('yuna08', 'Yuna', 77, 2);
UPDATE students SET score = min(score + 3, 100) WHERE class_id = 2;

changes: 3 で、組の点数は Sora 94、Leo 58、Yuna 80 になりました。引数2つの min() は小さいほうを返すので、99点は102ではなく100になります。

2. 生徒を先に移してからクラスを消すことを、1つのトランザクションにまとめます。

BEGIN;
UPDATE students SET class_id = 1 WHERE class_id = 2;   -- changes: 3
DELETE FROM classes WHERE name = '1-B';                -- changes: 1
COMMIT;

いきなり消そうとした試みは FOREIGN KEY constraint failed (19) でした。トランザクションの後、classes には 1-A だけが残り、生徒7人全員が1組になりました。

次回

第3回は複数のテーブルをまたいで読みます。JOIN、GROUP BY と HAVING、COUNT(*) と COUNT(列) が食い違う理由、合計を倍に膨らませる「行の爆発」、そしてサブクエリと JOIN のどちらが速いか（実測）を扱います。

• 前へ：SQL 講座 1/6：SELECT・WHERE・ORDER BY と NULL
• 次へ：SQL 講座 3/6：JOIN・GROUP BY・サブクエリ
• その後：インデックスと EXPLAIN QUERY PLAN、トランザクション・分離レベル・ロック、ウィンドウ関数と CTE
• 全体の学習順：新人エンジニアのためのプログラミング基礎学習ロードマップ
• コードから DB に安全に書き込む：PHP PDOとSQLite：SQLインジェクションを再現してから直す