Tech BlogAWSツール & 技術ブログ
メニュー

カテゴリ

AWS & AI ツール

タグ

AI29Claude Code27セキュリティ12AWS12Claude11効率化11開発ツール8プラグイン6Next.js5S35CloudFront5Opus 4.84すべて見る →

「制約があるから大丈夫」が崩れる瞬間——SQLiteのCHECK・MySQLのJSON_UNQUOTE・外部キー削除で踏んだ罠

SQLSQLiteMySQLデータベース設計トラブルシューティング

DBの制約は「書いておけば守られる」ものだと思いがちです。実際には、書き方を少し間違えるだけで制約が存在するのに機能していないという状態になり、しかもエラーが出ないので気づきにくいという厄介な性質があります。ここ最近、立て続けにこの手の罠を踏んだので、実機で検証したログとともに整理します。

Case 1: SQLiteのCHECK制約にNULLを混ぜると意味を失う

「NULLも許可したいCHECK制約」を書くとき、こう書いてしまうことがあります。

CREATE TABLE bad_example (
  id INTEGER PRIMARY KEY,
  owner TEXT CHECK (owner IN ('self','spouse','shared',NULL))
);

「リストにNULLを含めておけばNULLも許可されるはず」という発想ですが、実際に試すとこうなります。

sqlite> INSERT INTO bad_example (owner) VALUES ('self');
sqlite> INSERT INTO bad_example (owner) VALUES ('bogus_value_not_in_list');
sqlite> SELECT * FROM bad_example;
id  owner
--  -----------------------
1   self
2   bogus_value_not_in_list

リストに存在しない値 bogus_value_not_in_list がエラーなく通ってしまいました。CHECK制約はそもそも機能していません。

なぜ通るのか: SQLの3値論理

SQLの比較演算は TRUE / FALSE の2値ではなく、TRUE / FALSE / NULL（不明）の3値論理で動きます。IN 演算子は内部的に OR 比較の連鎖なので、リストにNULLが混ざると結果もこの3値論理に従います。

式 結果
'a' IN ('a','b',NULL) 1（TRUE）
'bogus' IN ('a','b',NULL) NULL（不明。FALSEではない）
'bogus' IN ('a','b') 0（FALSE）

実際に確認するとこうなります。

sqlite> SELECT 'bogus' IN ('a','b',NULL) AS result;
result
------
NULL

そしてSQLiteのCHECK制約は、結果がFALSEのときだけ拒否し、NULLやTRUEは通すという仕様です。 リストにない値を IN で判定した結果がNULL（不明）になるため、CHECK制約はこれを拒否すべき理由がないと判断してそのまま通してしまいます。リストにNULLを入れればNULLも許可されるという発想自体は正しいのですが、その副作用としてリストにない任意の値まで一緒に素通りするのが罠の本質です。

正しい書き方

NULL許可は IN のリストに混ぜず、IS NULL で明示的に分岐させます。

CREATE TABLE good_example (
  id INTEGER PRIMARY KEY,
  owner TEXT CHECK (owner IS NULL OR owner IN ('self','spouse','shared'))
);

同じ手順で試すと、今度はきちんと拒否されます。

sqlite> INSERT INTO good_example (owner) VALUES ('self');
sqlite> INSERT INTO good_example (owner) VALUES ('bogus_value_not_in_list');
Error: stepping, CHECK constraint failed: owner IS NULL OR owner IN ('self','spouse','shared') (19)

なお、既存テーブルのCHECK制約は ALTER TABLE では直せず、テーブルの再作成が必要です。アプリ側に別の入力検証が既にあるなら、実害は小さいので新規作成分だけ直して既存テーブルは放置する、という判断も十分ありです。

おまけ: 部分UNIQUEインデックスも罠がある

SQLiteでは論理削除されていない行だけを対象にした部分UNIQUEインデックス（WHERE deleted_at IS NULL 付き）を作ることがありますが、これは素の ON CONFLICT(列) では対象（conflict target）として解決されません。実際に試すとこうなります。

CREATE TABLE items (id INTEGER PRIMARY KEY, sku TEXT, deleted_at TEXT);
CREATE UNIQUE INDEX idx_active_sku ON items(sku) WHERE deleted_at IS NULL;
INSERT INTO items (sku, deleted_at) VALUES ('X1', NULL);

sqlite> INSERT INTO items (sku, deleted_at) VALUES ('X1', NULL) ON CONFLICT(sku) DO NOTHING;
Error: in prepare, ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

「一意制約に一致しない」という趣旨のエラーになります。正確には、conflict target側にも部分インデックスと同じ WHERE 句を付ければ一致します（これも実機で確認しました）。

sqlite> INSERT INTO items (sku, deleted_at) VALUES ('X1', NULL)
   ...>   ON CONFLICT(sku) WHERE deleted_at IS NULL DO NOTHING;
（エラーなし・重複行も挿入されない）

ただし、INSERT を書くたびに WHERE 句まで正確に一致させる必要があり、書き忘れれば構文エラーで気づけるとはいえ煩雑です。NULLを重複扱いしないUNIQUEインデックスの性質だけで足りる設計なら、部分インデックスをやめて素の UNIQUE 制約として書く方がシンプルに保てます。

Case 2: MySQLのJSON_UNQUOTEはJSON nullを文字列"null"で返す

もう一つ、似た「エラーが出ないのに意図と違う」系の罠がMySQLにもあります。JSONカラムから値を取り出してJSON_UNQUOTEで文字列化する処理はよく書きますが、JSON側のnullと、SQLのNULLは別物です。

Docker で mysql:8.0 を一時的に立てて実際に確認しました。

docker run -d --name mysql-json-test -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -e MYSQL_DATABASE=test mysql:8.0

SELECT
  JSON_EXTRACT('{"a": null}', '$.a') AS extract_null,
  JSON_UNQUOTE(JSON_EXTRACT('{"a": null}', '$.a')) AS unquote_null,
  JSON_UNQUOTE(JSON_EXTRACT('{"a": null}', '$.a')) IS NULL AS is_actually_null,
  JSON_UNQUOTE(JSON_EXTRACT('{"a": null}', '$.a')) = 'null' AS equals_string_null;

extract_null  unquote_null  is_actually_null  equals_string_null
null          null          0                 1

表示上はどちらも null に見えますが、IS NULL の結果は 0（false）、文字列 'null' との比較は 1（true）です。つまりこの値は本物のSQL NULLではなく、4文字の文字列としての nullです。

対比として、そもそもJSONパスが存在しない場合を見るとこの違いがはっきりします。

SELECT
  JSON_UNQUOTE(JSON_EXTRACT('{"a": null}', '$.b')) AS path_not_exist,
  JSON_UNQUOTE(JSON_EXTRACT('{"a": null}', '$.b')) IS NULL AS path_not_exist_is_null;

path_not_exist  path_not_exist_is_null
NULL            1

存在しないパス $.b を取り出した場合は、今度は本当にIS NULLが1になります。つまり同じJSON_UNQUOTEという関数から、キーが存在しない場合は本物のNULL、値がJSON nullである場合は文字列の nullという、非対称な2種類の結果が返ってくるということです。

これをアプリケーション側で if ($value === null) のような厳密比較でチェックしていると、JSON側で明示的にnullが入っているケースだけ判定を素通りします。エラーにも警告にもならず、ただ「想定と違う分岐に入る」だけなので、テストで両方のケース（キー欠落 / 値がnull）を分けて書いておかないと発見しにくいバグです。

Case 3: 参照テーブルを増やしたら、親の削除ルートを必ず見直す

3つ目は制約そのものではなく、制約を追加し忘れる話です。外部キーで親テーブル（例: カテゴリ）を参照する子テーブルを後から追加すると、親の削除処理は「追加前の子テーブルしか掃除していない」ため、外部キー制約違反で失敗するようになります。

実際に同じプロジェクトで同型のバグを2回踏みました。

  1. あるレコードを削除 → 後から追加した履歴テーブルの参照が残っていて失敗
  2. 別の親レコードを削除 → 後から追加した自動割当ルールテーブルの参照が残っていて失敗（1の修正時に「同じパターンの箇所」を横展開で探したにもかかわらず、その後に新設されたテーブルで再発）

教訓は、バグ修正のタイミングで横展開しても再発を防げないということです。横展開は今ある子テーブルしか対象にできないので、後から追加される子テーブルには効きません。代わりに、外部キー付きテーブルを新設した瞬間に、参照先の親の削除ルートを見直すというチェックを、スキーマ追加時のレビュー項目として固定するのが実効性のある対策です。

従属データ（履歴・自動化設定など）を親と一緒に消してよいか、残すべきかはテーブルごとに判断が分かれるところなので、その判断結果をテストで固定しておくと安心です。個人的には ON DELETE CASCADE に頼るより、アプリ側で明示的に削除する方を選んでいます。少人数運用では「削除時に何が一緒に消えるか」がコードを読むだけで分かる方が、DB制約のメタデータを別途確認するより安全に感じるからです。

まとめ: 「制約がある」と「意図通りに効く」は別

3つのケースに共通しているのは、制約自体は存在しているのに、意図した通りには機能していないという点です。

  • SQLiteのCHECK制約: 3値論理により、NULLを許可しようとした書き方が任意の値まで通してしまう
  • MySQLのJSON_UNQUOTE: 「キー欠落」と「値がnull」が非対称に扱われ、片方だけ本物のNULLになる
  • 外部キー: 制約自体は正しくても、参照する側のテーブルが後から増えると、既存の削除ロジックが追従していない

いずれも実行時エラーやアプリのクラッシュという形では出ず、「本来拒否されるべき値が通る」「本来nullとして処理すべき値が文字列として扱われる」「レビュー時には気づかれず、しばらく経ってから削除操作が失敗する」という静かな形で表面化します。CHECK制約やJSON関数の挙動は一度手元で sqlite3 やDockerのmysqlコンテナを立てて実際に試してみると、ドキュメントを読むだけより体感として残りやすいので、疑わしいと思ったら実機で確認する習慣をつけておくのがおすすめです。

関連記事

← 記事一覧に戻る