NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る

SQLの制約。データベースの前でデータカードを確認するゲート、盾とチェック。

アプリで弾いたはずの値が入ります。NOT NULL・UNIQUE・CHECK制約を、データベース側に置きます。

前回: DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない これは【深掘り】です。本線の順路ではありません。 本線のテーブル設計と正規化|更新・挿入・削除の異常を実際に起こすで、外部キーと ON DELETE を張りました。この記事はその回の続きで、外部キーを含む「制約」全体を一段深く見にいくものです。飛ばしても本線は繋がります。

作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて appdb があれば、他に用意するものはありません。練習用のテーブルはこの記事の中で作ります。

アプリの登録フォームには、こう書いてあります。

if (!name || name.trim() === '') return res.status(400).json({ error: '名前は必須です' });
if (qty <= 0) return res.status(400).json({ error: '数量は1以上です' });

書いてあるので、変な値は入らないはずでした。

半年後、テーブルを SELECT して、こうなっていました。

+-----------+-------------+------------------+---------+------+
| member_id | member_name | email            | dept_id | qty  |
+-----------+-------------+------------------+---------+------+
|         1 | 田中        | tanaka@example.com |       1 |    3 |
|         2 |             | sato@example.com   |       1 |    5 |
|         3 | 鈴木        | tanaka@example.com |     999 |   -2 |
+-----------+-------------+------------------+---------+------+

名前が空です。メールアドレスが重複しています。存在しない部署の番号が入っています。数量がマイナスです。

アプリのコードは、いまも正しく動いています。入口が1つではなかった、というだけです。

  • アプリは1つとは限りません。 管理画面、バッチ処理、CSV取り込み、別チームが書いた別のサービス。同じ表に、別のコードが書き込みます
  • 手でSQLを打つ日が来ます。 障害対応で UPDATE を打った深夜、WHERE を間違えていました
  • アプリは書き換えられます。 リファクタで消えたバリデーションに、誰も気づきません

アプリの検証は「速く・親切に」断るためのものです。 断れなかったときに最後に止められるのは、データベースだけです。

この記事は、その最後の砦を張りにいきます。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • NOT NULL は「空を禁止する」ではないこと。空文字 '' は NULL ではないので、NOT NULL を素通りする
  • UNIQUE を張っても、NULL は何個でも入ること。MySQL はこれをエラーにしない。 「重複禁止にしたはずなのに」の正体はほぼこれ
  • 主キーは NOT NULL + UNIQUE に名前が付いただけではないこと。InnoDB では主キーの並び順が、データそのものの並び順と索引の効き方を決める
  • CHECK は NULL を弾かないこと。qty > 0 に NULL を入れると、真でも偽でもない「不明」になり、行は通ってしまう
  • INSERT IGNORE は「重複を無視する」だけではないこと。NOT NULL 違反も外部キー違反も文字切り詰めも、まとめて握り潰して先へ進む
  • 制約に名前を付けないと、エラーメッセージから原因にたどり着けないこと。そして名前が無いと、消すこともできない

この記事でやること

  1. MySQL のバージョンを先に確かめる(CHECK が実際に効くのは 8.0.16 以降。それ未満はエラーも出さずに無視する)
  2. 制約を1つも付けない表を作り、ゴミを4種類わざと入れる。 何が入るのかを先に目で見る
  3. 同じ表を、制約を付けて作り直す。 さっきと同じ INSERT が、4つとも別々のエラーで止まることを確かめる
  4. UNIQUE に NULL を何個も入れる。 複合ユニークの列の順番を EXPLAIN で確かめ、ON DUPLICATE KEY UPDATE と INSERT IGNORE を打ち分ける
  5. CHECK で負の数量を弾き、複数列にまたがる条件を書き、NULL を混ぜて素通りさせる
  6. すでに違反しているデータが入っている表に、あとから制約を足して失敗させる。 先に違反行を数える型を身につける

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
制約(せいやく / constraint)表に付ける「この条件を満たさない行は受け取らない」というルール。 アプリではなくデータベース側が持つ
NOT NULLその列を空のまま(NULL)にできないという制約
UNIQUE(一意制約)その列の値が、表の中で重複してはいけないという制約
主キー(PRIMARY KEY)その行を1行に特定するための列。 NOT NULL かつ UNIQUE。表に1つだけ
外部キー(FOREIGN KEY)別の表に存在する値しか入れさせないという制約。本線の回で扱った
CHECK 制約自分で書いた条件式が真になる行しか受け取らないという制約
DEFAULT列を省略して INSERT したときに、代わりに入る値
代理キー(サロゲートキー)意味を持たない連番を、主キーとして別に用意すること
クラスタ化索引InnoDB が、データそのものを主キーの順に並べて持っていること。主キーが表の物理的な並び順になる
三値論理条件の答えが真・偽・不明の3つあるという考え方。SELECTの回で扱った

① 先にバージョンを確かめる

この記事の後半は、MySQL 8.0.16 以降でないと動きません。

CHECK 制約は、MySQL 5.7 でも「構文としては」書けます。 エラーになりません。そして、何も起こしません。

これがこの記事でいちばん危ない罠です。 CHECK (qty > 0) と書いた表に -5 を入れて、エラーが出ないので「弾かれなかった=条件の書き方が悪い」と思ってしまいます。 実際には、MySQL 5.7 は CHECK を読み飛ばしています。 構文解析はするが、保存も検証もしません。 バージョンを見ないと、この事実には一生たどり着けません。

VMの中で、まず管理者で MySQL に入ります。

bash
$ sudo mysql
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.43    |
+-----------+

8.0.16 以上であることを確認してください。 8.0.15 以下や 5.7.x だった場合、この記事の CHECK の節は動きません(NOT NULL / UNIQUE / 主キー / 外部キーの節は動きます)。

[スクリーンショット: 01_mysqlバージョン確認.png] sudo mysql で入って SELECT VERSION(); を実行し、8.0 系のバージョンが返っている画面を撮ってください。

もう1つ、先に見ておく設定があります。

SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...             |
+-----------------------------------------------------------------------+

STRICT_TRANS_TABLES が入っていることを確認してください。 MySQL 8.0 の既定では入っています。

これが無いと、制約違反が「エラー」ではなく「警告」になります。 複数行の INSERT や INSERT … SELECT で NOT NULL の列に NULL を入れると、エラーにならず、勝手に 0 や '' が入って成功します(1行だけの INSERT は、この設定が無くてもエラーになります)。 昔の MySQL が「何を入れても通る」と言われたのは、この設定のせいです。外さないでください。

そして、作業するデータベースを選びます。

USE appdb;

触るのは d_ct_ で始まる表だけにする

本線で作った tasks や users には一切触りません。

この記事で作る表は、すべて d_ct_ で始めます(d は deep、ct は constraint のつもりです)。

  • SHOW TABLES; を打ったときに、この記事の練習用だとひと目で分かる
  • 消したくなったときに、本線の表を巻き込まない

名前の規則を先に決めておくのは、DROP の事故を防ぐためです。

SELECTの深掘りで作った d_emp / d_dept があっても、この記事とは無関係です。 名前がぶつからないようにしてあるので、そのまま置いておいて構いません。


② 制約が無い表には、何でも入る

先に「制約が無いとどうなるか」を見ます。 ここを飛ばして制約の書き方から入ると、何のために書いているのか分からないまま暗記になります。

制約を1つも書かない表

DROP TABLE IF EXISTS d_ct_loose;

CREATE TABLE d_ct_loose (
  member_id   INT,
  member_name VARCHAR(40),
  email       VARCHAR(80),
  dept_id     INT,
  qty         INT
);

NOT NULL も PRIMARY KEY も UNIQUE も CHECK も、1つも書いていません。 列と型だけです。

4種類のゴミを入れる

INSERT INTO d_ct_loose VALUES (1, '田中', 'tanaka@example.com', 1, 3);
INSERT INTO d_ct_loose VALUES (2, '',     'sato@example.com',   1, 5);
INSERT INTO d_ct_loose VALUES (3, '鈴木', 'tanaka@example.com', 1, 3);
INSERT INTO d_ct_loose VALUES (4, '高橋', 'takahashi@example.com', 999, -2);
INSERT INTO d_ct_loose VALUES (1, NULL,   NULL, NULL, NULL);

5行とも通ります。 1つもエラーが出ません。

SELECT * FROM d_ct_loose;
+-----------+-------------+------------------------+---------+------+
| member_id | member_name | email                  | dept_id | qty  |
+-----------+-------------+------------------------+---------+------+
|         1 | 田中        | tanaka@example.com     |       1 |    3 |
|         2 |             | sato@example.com       |       1 |    5 |
|         3 | 鈴木        | tanaka@example.com     |       1 |    3 |
|         4 | 高橋        | takahashi@example.com  |     999 |   -2 |
|         1 | NULL        | NULL                   |    NULL | NULL |
+-----------+-------------+------------------------+---------+------+

入ったものを、1つずつ見てください。

入った値何が壊れているか
2行目 member_name が ''名前が空の会員。 画面には空白が出る。誰なのか永久に分からない
3行目 email が1行目と同じ重複したメールアドレス。 パスワード再発行のメールが2人に届く
4行目 dept_id が 999存在しない部署ID。 JOIN すると行ごと消える(JOINの深掘りで見た現象)
4行目 qty が -2負の数量。 合計金額がマイナスになる。返金処理が走る
5行目 member_id が 1 で重複同じIDの行が2つ。 WHERE member_id = 1 が2行返る。アプリは1行しか想定していない

[スクリーンショット: 02_制約なしの表に入った5行.png] SELECT * FROM d_ct_loose; の結果を撮ってください。空文字・重複メール・999・-2 が並んでいる状態です。

ここが出発点です。 「アプリで弾いているから大丈夫」は、この表を守っていません。 この5行は、sudo mysql から手で打てば、いつでも入ります。バッチ処理も、CSV取り込みも、同じ入口を使います。

ここから、この5行を1つずつ入れなくするのが、この記事の残り全部です。


③ NOT NULL — 空文字は NULL ではない

親の表を先に作る

まず、部署の表を作ります。外部キーの相手になるので、親から先に作ります(理由は⑥で書きます)。

DROP TABLE IF EXISTS d_ct_order;
DROP TABLE IF EXISTS d_ct_member;
DROP TABLE IF EXISTS d_ct_dept;

CREATE TABLE d_ct_dept (
  dept_id   INT         NOT NULL,
  dept_name VARCHAR(40) NOT NULL,
  PRIMARY KEY (dept_id)
) ENGINE=InnoDB;

INSERT INTO d_ct_dept VALUES (1, '営業'), (2, '開発'), (3, '総務');

NULL は入らない

INSERT INTO d_ct_dept VALUES (4, NULL);
ERROR 1048 (23000): Column 'dept_name' cannot be null

止まりました。 これが NOT NULL の仕事です。

しかし、空文字は入る

INSERT INTO d_ct_dept VALUES (4, '');
Query OK, 1 row affected (0.00 sec)

通ってしまいました。

SELECT dept_id, dept_name, CHAR_LENGTH(dept_name) AS len FROM d_ct_dept;
+---------+-----------+-----+
| dept_id | dept_name | len |
+---------+-----------+-----+
|       1 | 営業      |   2 |
|       2 | 開発      |   2 |
|       3 | 総務      |   2 |
|       4 |           |   0 |
+---------+-----------+-----+

NOT NULL は「空を禁止する」制約ではありません。 NULL(値が無いという状態)を禁止する制約です。 ''(長さ0の文字列)は、れっきとした値です。 「無い」のではなく「0文字ある」。だから NOT NULL は通します。

これは MySQL の癖ではなく、SQL としてそういうものです。 SELECTの深掘りで書いた「NULL は空っぽの値ではなく、値がないという状態」が、そのまま効いてきます。

「空文字も禁止したい」なら、NOT NULL では足りません。 ⑦の CHECK でやります。

いまの行は消しておきます。

DELETE FROM d_ct_dept WHERE dept_id = 4;

DEFAULT との組み合わせ

NOT NULL を付けると、その列は INSERT のたびに必ず書かないといけなくなります。 それが面倒なので DEFAULT を併記します。

CREATE TABLE d_ct_member (
  member_id   INT          NOT NULL AUTO_INCREMENT,
  member_name VARCHAR(40)  NOT NULL,
  email       VARCHAR(80),
  dept_id     INT,
  status      VARCHAR(10)  NOT NULL DEFAULT 'active',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (member_id)
) ENGINE=InnoDB;

status と created_at は書かなくても入ります。

INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('田中', 'tanaka@example.com', 1);
SELECT member_id, member_name, status, created_at FROM d_ct_member;
+-----------+-------------+--------+---------------------+
| member_id | member_name | status | created_at          |
+-----------+-------------+--------+---------------------+
|         1 | 田中        | active | 2026-09-18 10:12:44 |
+-----------+-------------+--------+---------------------+

ここに、必ず1回踏む落とし穴があります。

INSERT INTO d_ct_member (member_name, status) VALUES ('佐藤', NULL);
ERROR 1048 (23000): Column 'status' cannot be null

DEFAULT は「NULL を入れたら代わりに使われる値」ではありません。 列そのものを省略したときにだけ使われます。明示的に NULL と書いたら、それは「NULL を入れたい」という意思表示なので、NOT NULL に弾かれます。

アプリ側でこれをやりがちです。req.body.status が undefined のとき、ドライバがそれを NULL に変換して送ります。「省略した」つもりが「NULL を明示した」になっています。 直し方は、値が無いときはその列をSQLに含めないことです。ORMとマイグレーションの回で使った Knex なら、insert() に渡すオブジェクトからキーごと落とします。

DEFAULT を使うか、NULL を許すかは、意味で決めます。

状況設計
必ず値があり、初期値が決まっているNOT NULL DEFAULT 'active'
「まだ決まっていない」を表したいNULL を許す(DEFAULT を付けない)
必ず値があり、初期値は決められないNOT NULL(DEFAULT なし)。書き忘れをエラーにしたいとき

3つ目を選ぶ勇気が要ります。 「とりあえず DEFAULT ''」を付けると、書き忘れが空文字として静かに保存されます。


④ UNIQUE — NULL は何個でも入る

まず重複を止める

ALTER TABLE d_ct_member
  ADD CONSTRAINT uq_ct_member_email UNIQUE (email);

同じメールアドレスをもう1件入れてみます。

INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('鈴木', 'tanaka@example.com', 1);
ERROR 1062 (23000): Duplicate entry 'tanaka@example.com' for key 'd_ct_member.uq_ct_member_email'

止まりました。 そしてエラーの中に uq_ct_member_email という名前が出ています。 これが⑧の伏線です。

ところが、NULL は何個でも入る

INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('高橋', NULL, 2);
INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('伊藤', NULL, 2);
INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('渡辺', NULL, 3);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

3行とも通りました。

SELECT member_id, member_name, email FROM d_ct_member;
+-----------+-------------+--------------------+
| member_id | member_name | email              |
+-----------+-------------+--------------------+
|         1 | 田中        | tanaka@example.com |
|         3 | 高橋        | NULL               |
|         4 | 伊藤        | NULL               |
|         5 | 渡辺        | NULL               |
+-----------+-------------+--------------------+

これが、この記事でいちばん多くの人が踏む罠です。 UNIQUE は「同じ値が2つあってはいけない」制約です。 そして NULL は「値」ではありません。 NULL と NULL を = で比べても、答えは真になりません(不明です)。「同じかどうか判定できない」ものは、重複と判定されません。 だから UNIQUE 列には NULL が何個でも入ります。

これは MySQL の不具合ではなく、標準SQLがそう決めています。 PostgreSQL も既定で同じ動きをします(PostgreSQLの回で触れた「違いは1割」の、同じ側の話です)。

「1件しか入ってはいけない」なら、UNIQUE だけでは足りません。

ALTER TABLE d_ct_member MODIFY email VARCHAR(80) NOT NULL;

いまは通りません。 NULL の行が3つあるからです。

ERROR 1138 (22004): Invalid use of NULL value

これが⑨で扱う「既存データが違反しているとき」です。 先に進むので、いまは NULL のままにしておきます。

設計としてどちらが正しいかは、意味で決まります。 – メールアドレスを持たない会員がいる → NULL を許す。そのかわり「未登録が何人いるか」をアプリ側で意識する – 全員が必ず持つ → NOT NULL + UNIQUE の両方を付ける。片方だけでは守れません

複合ユニーク — 列の順番には意味がある

「同じ部署の中で、同じ名前は1人まで」を表したいとします。1列では表せません。

ALTER TABLE d_ct_member
  ADD CONSTRAINT uq_ct_member_dept_name UNIQUE (dept_id, member_name);
INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('高橋', 'takahashi@example.com', 2);
ERROR 1062 (23000): Duplicate entry '2-高橋' for key 'd_ct_member.uq_ct_member_dept_name'

2-高橋 という組み合わせで重複と判定されました。 部署が違えば通ります。

INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('高橋', 'takahashi@example.com', 3);
Query OK, 1 row affected (0.00 sec)

ここで、順番の話です。 UNIQUE (dept_id, member_name) と UNIQUE (member_name, dept_id) は、制約としてはまったく同じ意味です。 「この2つの組み合わせが重複しない」ことしか言っていません。

しかし、索引としては別物です。

UNIQUE を張ると、MySQL は裏で索引(インデックス)を1本作ります。 制約の実現手段が索引だからです。確かめます。

SHOW INDEX FROM d_ct_member;
+--------------+------------+------------------------+--------------+-------------+
| Table        | Non_unique | Key_name               | Seq_in_index | Column_name |
+--------------+------------+------------------------+--------------+-------------+
| d_ct_member  |          0 | PRIMARY                |            1 | member_id   |
| d_ct_member  |          0 | uq_ct_member_email     |            1 | email       |
| d_ct_member  |          0 | uq_ct_member_dept_name |            1 | dept_id     |
| d_ct_member  |          0 | uq_ct_member_dept_name |            2 | member_name |
+--------------+------------+------------------------+--------------+-------------+

Seq_in_index が、索引の中での列の順番です。 そして、複合索引は「左から順に使う」しかできません。

EXPLAIN SELECT * FROM d_ct_member WHERE dept_id = 2;
EXPLAIN SELECT * FROM d_ct_member WHERE dept_id = 2 AND member_name = '高橋';
EXPLAIN SELECT * FROM d_ct_member WHERE member_name = '高橋';

上2つは key に uq_ct_member_dept_name が出ます。 3つ目は key が NULL になり、type が ALL(全件走査)になります。

電話帳を思い出してください。 「姓 → 名」の順で並んでいる本で、 「佐藤」から探すのは速い、「佐藤 太郎」も速い。でも「太郎」だけでは引けません。 UNIQUE (dept_id, member_name) は、まさにその本です。

だから列の順番は、「どちらで検索することが多いか」で決めます。 制約としては同じでも、片方の順番だけが検索を速くします。 索引の読み方はEXPLAINの回で扱いました。

ON DUPLICATE KEY UPDATE — 重複したら更新する

「あれば更新、無ければ挿入」を1文で書けます。

INSERT INTO d_ct_member (member_name, email, dept_id)
VALUES ('田中', 'tanaka@example.com', 2) AS new
ON DUPLICATE KEY UPDATE dept_id = new.dept_id;
Query OK, 2 rows affected (0.01 sec)

2 rows affected は誤植ではありません。 MySQL は、挿入されたときは 1、更新されたときは 2 を返します(削除はしておらず、「更新した」ことを区別するための決まりです)。「2件更新された」ではありません。

SELECT member_id, member_name, email, dept_id FROM d_ct_member WHERE email = 'tanaka@example.com';
+-----------+-------------+--------------------+---------+
| member_id | member_name | email              | dept_id |
+-----------+-------------+--------------------+---------+
|         1 | 田中        | tanaka@example.com |       2 |
+-----------+-------------+--------------------+---------+

新しい行は増えず、既存の行の dept_id が 1 から 2 に変わりました。

注意点が2つあります。

  • 「重複」の判定は、主キーと UNIQUE のどれかに当たったかどうかです。 どの制約に当たったのかは自分で選べません。UNIQUE が2本ある表では、意図しないほうの行が更新されます
  • AUTO_INCREMENT の番号は、更新のときでも消費されます。 番号が飛びますが、これは異常ではありません

INSERT IGNORE — 他のエラーまで握り潰す

INSERT IGNORE は「重複していたら黙って飛ばす」と説明されます。それは説明の半分です。

INSERT IGNORE INTO d_ct_member (member_name, email, dept_id) VALUES ('田中2号', 'tanaka@example.com', 1);
Query OK, 0 rows affected, 1 warning (0.00 sec)

ここまでは想定どおりです。 問題はここからです。

INSERT IGNORE INTO d_ct_dept (dept_id, dept_name) VALUES (9, NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

1 row affected です。入りました。 NOT NULL の列に NULL を入れたのに、です。

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'dept_name' cannot be null           |
+---------+------+--------------------------------------------+
SELECT dept_id, dept_name, CHAR_LENGTH(dept_name) AS len FROM d_ct_dept WHERE dept_id = 9;
+---------+-----------+-----+
| dept_id | dept_name | len |
+---------+-----------+-----+
|       9 |           |   0 |
+---------+-----------+-----+

IGNORE は「重複を無視する」ではなく、「エラーを警告に格下げする」修飾子です。 格下げされるのは重複だけではありません。

– NOT NULL 違反 → 上のとおり、空文字や 0 が勝手に入る – 外部キー違反 → 行が黙って捨てられる – VARCHAR(40) に50文字 → 40文字に切り詰められて保存される – 数値の桁あふれ・不正な日付 → 丸められた値が入る

CSV取り込みのスクリプトに INSERT IGNORE と書いてあるのを見たら、疑ってください。 「エラーが出ないので成功した」と思っていた取り込みが、半分の行を捨て、残りの半分を切り詰めていた、というのは実際に起きます。

IGNORE を使うなら、必ず件数を突き合わせます。

SELECT ROW_COUNT();
SHOW WARNINGS;

「入れようとした件数」と ROW_COUNT() が一致しないなら、どこかで行が消えています。 数え方の型はSELECTの深掘りと同じで、説明できない数字が出たらそこに原因がいます。

後片づけとして、この行は消しておきます。

DELETE FROM d_ct_dept WHERE dept_id = 9;

⑤ 主キー — NOT NULL + UNIQUE に、もう1つ

まず定義から

主キーは、NOT NULL かつ UNIQUE です。 確かめます。

DROP TABLE IF EXISTS d_ct_pk;
CREATE TABLE d_ct_pk (
  id   INT,
  memo VARCHAR(20),
  PRIMARY KEY (id)
);

SHOW CREATE TABLE d_ct_pk\G
       Table: d_ct_pk
Create Table: CREATE TABLE `d_ct_pk` (
  `id` int NOT NULL,
  `memo` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

id INT としか書いていないのに、int NOT NULL になっています。 PRIMARY KEY を付けると、MySQL がその列を勝手に NOT NULL にします。 これは書き忘れの補完ではなく、主キーの定義そのものです。「その行を1行に特定する」ものが NULL では、特定できません。

表に主キーは1つだけです。UNIQUE は何本でも張れます。

PRIMARY KEY だけは、名前を付けても無視される

DROP TABLE IF EXISTS d_ct_pk;
CREATE TABLE d_ct_pk (
  id   INT,
  memo VARCHAR(20),
  CONSTRAINT pk_ct_pk_id PRIMARY KEY (id)
);

SHOW INDEX FROM d_ct_pk;
+---------+------------+----------+--------------+-------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name |
+---------+------------+----------+--------------+-------------+
| d_ct_pk |          0 | PRIMARY  |            1 | id          |
+---------+------------+----------+--------------+-------------+

pk_ct_pk_id という名前はどこにも残っていません。PRIMARY になっています。

MySQL では、主キーの名前は必ず PRIMARY です。 付けた名前は捨てられます(エラーにはなりません)。 ⑧で「制約に名前を付けろ」と書きますが、主キーだけは例外です。 名前を付けても意味がないので、PRIMARY KEY (id) とだけ書けば十分です。

主キーの順番が、索引の効き方を決める

ここが、主キーが「ただの NOT NULL + UNIQUE」ではない理由です。

InnoDB は、データそのものを主キーの順に並べて持っています(クラスタ化索引)。主キーは「行を特定する印」であると同時に、「表の物理的な並び順」です。

複合主キーを作って確かめます。 注文明細のように、「注文番号 + 行番号」で1行が決まる表です。

DROP TABLE IF EXISTS d_ct_order;

CREATE TABLE d_ct_order (
  order_id   INT NOT NULL,
  line_no    INT NOT NULL,
  member_id  INT NOT NULL,
  qty        INT NOT NULL,
  unit_price INT NOT NULL,
  discount   INT NOT NULL DEFAULT 0,
  ship_date  DATE,
  due_date   DATE,
  PRIMARY KEY (order_id, line_no)
) ENGINE=InnoDB;

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES
  (1001, 1, 1, 2, 500),
  (1001, 2, 1, 1, 1200),
  (1002, 1, 3, 3, 300),
  (1003, 1, 4, 1, 800);
SELECT * FROM d_ct_order;

ORDER BY を書いていないのに、order_id, line_no の順に並んで出てきます。 偶然ではありません。その順で保存されているからです。

そして、複合ユニークと同じ「左から順に」が効きます。

EXPLAIN SELECT * FROM d_ct_order WHERE order_id = 1001;
EXPLAIN SELECT * FROM d_ct_order WHERE line_no = 1;

上は主キーが効きます(type: ref)。下は効きません(type: ALL)。

PRIMARY KEY (order_id, line_no) と PRIMARY KEY (line_no, order_id) は、制約としては同じです。 どちらも「この2つの組み合わせが1回だけ」を保証します。 しかし前者は「ある注文の明細を全部取る」が速く、後者は絶望的に遅くなります。 主キーの列の順番は、いちばん外側でまとめて取りたいものを先に置きます。

複合主キーか、代理キーか

同じ表は、こうも作れます。

-- 代理キー版(この記事では作りません。形だけ)
--   id        INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
--   order_id  INT NOT NULL,
--   line_no   INT NOT NULL,
--   UNIQUE (order_id, line_no)

意味のない連番 id を主キーにして、本当の一意性は UNIQUE で守るやり方です。これを代理キー(サロゲートキー)と呼びます。

複合主キー代理キー + UNIQUE
一意性の保証できるできる(UNIQUE が担当)
子表から参照するとき2列まとめて持つ必要がある。子が増えるほど太る1列で済む
副次索引の大きさ副次索引は主キーを丸ごと抱える。 複合だと全部の列を持つので太る細い
ORMとの相性面倒。多くのORMが単一主キーを前提にする良い
並び順業務的に意味のある順で保存される挿入順で保存される

どちらが正しい、はありません。 私は迷ったときは代理キー + UNIQUE にしています。 理由は「一意性の定義があとから変わっても、UNIQUE を張り替えるだけで済む」からです。主キーを変えると、それを参照している全部の表を直すことになります。 連番にするか UUID にするかはテーブル設計と正規化の回で扱ったので、そちらを見てください。


⑥ 外部キー — 本線で扱った先の、4つだけ

外部キーの基本と ON DELETE はテーブル設計と正規化の回で扱いました。 ここでは、そこに書かなかった4つだけを足します。

まず張ります。

ALTER TABLE d_ct_member
  ADD CONSTRAINT fk_ct_member_dept
  FOREIGN KEY (dept_id) REFERENCES d_ct_dept (dept_id);

ALTER TABLE d_ct_order
  ADD CONSTRAINT fk_ct_order_member
  FOREIGN KEY (member_id) REFERENCES d_ct_member (member_id);

存在しない部署は、もう入りません。

INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('山本', 'yamamoto@example.com', 999);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`appdb`.`d_ct_member`, CONSTRAINT `fk_ct_member_dept` FOREIGN KEY (`dept_id`) REFERENCES `d_ct_dept` (`dept_id`))

②の4行目が入らなくなりました。

(1) 型と照合順序が一致しないと、そもそも張れない

「なぜか外部キーが作れない」の原因は、ほぼこれです。

DROP TABLE IF EXISTS d_ct_code_child;
DROP TABLE IF EXISTS d_ct_code;

CREATE TABLE d_ct_code (
  code VARCHAR(10) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE d_ct_code_child (
  id   INT NOT NULL PRIMARY KEY,
  code VARCHAR(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  CONSTRAINT fk_ct_code FOREIGN KEY (code) REFERENCES d_ct_code (code)
) ENGINE=InnoDB;
ERROR 3780 (HY000): Referencing column 'code' and referenced column 'code' in foreign key constraint
'fk_ct_code' are incompatible.

列の名前も長さも同じです。違うのは文字コードと照合順序だけで、張れません。

外部キーは、値を突き合わせて存在を確かめる仕組みです。 「同じ値かどうか」の判定ルールが両側で違ったら、突き合わせようがありません。 照合順序はまさにその判定ルールです(文字コードの回で扱いました)。

整数でも同じことが起きます。 親が INT、子が BIGINT や INT UNSIGNED だと、同じ 3780 で止まります。 「なんとなく大きめの型にしておいた」が、後から効いてきます。

確かめ方は、両方の SHOW CREATE TABLE を並べることです。

SHOW CREATE TABLE d_ct_code\G
SHOW CREATE TABLE d_ct_code_child\G

型・符号・文字コード・照合順序の4つが完全に一致していることを見ます。エラー番号だけ見ても原因は分からないので、エラーの読み方の回と同じで、メッセージに出ている列名を手がかりに定義を見にいきます。

いまの失敗したテーブルは消しておきます。

DROP TABLE IF EXISTS d_ct_code_child;
DROP TABLE IF EXISTS d_ct_code;

(2) 外部キーを張ると、索引が勝手に増える

SHOW INDEX FROM d_ct_order;
+------------+------------+--------------------+--------------+-------------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name |
+------------+------------+--------------------+--------------+-------------+
| d_ct_order |          0 | PRIMARY            |            1 | order_id    |
| d_ct_order |          0 | PRIMARY            |            2 | line_no     |
| d_ct_order |          1 | fk_ct_order_member |            1 | member_id   |
+------------+------------+--------------------+--------------+-------------+

fk_ct_order_member という索引を、頼んでいないのに作られています。

理由は、外部キーの検査を毎回やる必要があるからです。 親を消そうとするたびに「この親を指している子はいるか」を調べます。索引が無いと、そのたびに子の表を全件走査します。 だから MySQL は、外部キーを張るときに索引が無ければ勝手に作ります。

ここから2つ言えます。 – 同じ列に自分でもう1本索引を張らないこと。 重複した索引は、書き込みを遅くするだけです – 外部キーはタダではないこと。 索引が1本増え、INSERT / UPDATE / DELETE のたびに親の確認が走ります

(3) 親を先に入れる — 順番が固定される

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES (2001, 1, 777, 1, 100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

会員 777 がいないので、注文が入りません。 当たり前に見えますが、これはデータ投入の順番を固定します。

操作順番
INSERT親(d_ct_dept) → 子(d_ct_member) → 孫(d_ct_order)
DELETE逆。孫 → 子 → 親
DROP TABLE逆。孫 → 子 → 親

この記事の先頭で DROP TABLE IF EXISTS d_ct_order; を d_ct_dept より先に書いていたのは、これが理由です。 逆にすると、こうなります。

ERROR 3730 (HY000): Cannot drop table 'd_ct_dept' referenced by a foreign key constraint
'fk_ct_member_dept' on table 'd_ct_member'.

(4) FOREIGN_KEY_CHECKS — 一時的に外す、戻し忘れる

バックアップの復元や大量投入では、親子の順番を守れないことがあります。 そのとき、検査を一時的に止められます。

SELECT @@FOREIGN_KEY_CHECKS;
+----------------------+
| @@FOREIGN_KEY_CHECKS |
+----------------------+
|                    1 |
+----------------------+
SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES (2001, 1, 777, 1, 100);

SET FOREIGN_KEY_CHECKS = 1;

会員 777 がいないのに、注文が入りました。

危険が2つあります。両方とも実際に踏みます。

1つ目:戻し忘れ。 FOREIGN_KEY_CHECKS はその接続の中だけの設定です。接続を切れば 1 に戻ります。 問題は、切らずに作業を続けたときです。「なぜか外部キーが効かない」状態のまま、その日の残りの作業を全部やることになります。 SET ... = 0 と SET ... = 1 は、必ずセットで書いてから実行してください。 先に 0 だけ打って、途中で別の調べ物を始めるのが典型的な事故です。

2つ目:戻しても、検査はされない。 SET FOREIGN_KEY_CHECKS = 1; は「これから先を検査する」だけです。 0 の間に入った壊れた行は、そのまま残ります。 エラーも警告も出ません。 表は「外部キーが張ってあるのに、参照が壊れた行を持っている」状態になります。

だから、戻したあとに自分で確かめます。

SELECT o.order_id, o.line_no, o.member_id
FROM d_ct_order o
LEFT JOIN d_ct_member m ON o.member_id = m.member_id
WHERE m.member_id IS NULL;
+----------+---------+-----------+
| order_id | line_no | member_id |
+----------+---------+-----------+
|     2001 |       1 |       777 |
+----------+---------+-----------+

出ました。 これが「親のいない子」です。LEFT JOIN して IS NULL で拾う型は、JOINの深掘りで使ったものと同じです。

片づけます。

DELETE FROM d_ct_order WHERE order_id = 2001;

FOREIGN_KEY_CHECKS = 0 を使ったら、必ずこの LEFT JOIN を打つ、をセットにしてください。 打たないなら、最初から使わないほうが安全です。


⑦ CHECK 制約

ここまでで止められていないのが、②の「数量が -2」と「名前が空文字」です。 どちらも NOT NULL でも UNIQUE でも外部キーでも止まりません。CHECK の出番です。

この節は MySQL 8.0.16 以降でないと動きません。 ①で確かめたバージョンをもう一度見てください。

負の数量を弾く

ALTER TABLE d_ct_order
  ADD CONSTRAINT ck_ct_order_qty CHECK (qty > 0);
INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES (1004, 1, 1, -2, 500);
ERROR 3819 (HY000): Check constraint 'ck_ct_order_qty' is violated.

止まりました。 0 も入りません。

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES (1004, 1, 1, 0, 500);
ERROR 3819 (HY000): Check constraint 'ck_ct_order_qty' is violated.

[スクリーンショット: 03_check制約のエラー.png] qty に -2 を入れて ERROR 3819 ... Check constraint 'ck_ct_order_qty' is violated. が出ている画面を撮ってください。

空文字を弾く

③で「NOT NULL は空文字を止めない」と書きました。CHECK なら止まります。

ALTER TABLE d_ct_member
  ADD CONSTRAINT ck_ct_member_name CHECK (CHAR_LENGTH(TRIM(member_name)) > 0);
INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('', 'blank@example.com', 1);
INSERT INTO d_ct_member (member_name, email, dept_id) VALUES ('   ', 'space@example.com', 1);
ERROR 3819 (HY000): Check constraint 'ck_ct_member_name' is violated.
ERROR 3819 (HY000): Check constraint 'ck_ct_member_name' is violated.

空文字も、空白だけも止まりました。 TRIM() で前後の空白を落としてから長さを見ています。「空白1文字だけの名前」は、アプリのバリデーションでもよく漏れます。

決まった値だけを許す

ALTER TABLE d_ct_member
  ADD CONSTRAINT ck_ct_member_status CHECK (status IN ('active', 'paused', 'left'));
UPDATE d_ct_member SET status = 'ACTIVE' WHERE member_id = 1;
ERROR 3819 (HY000): Check constraint 'ck_ct_member_status' is violated.

大文字だと弾かれました。 表記ゆれが混ざらなくなります。

CHECK に書けないものがあります。 – 副問い合わせ(SELECT) — 他の表を見にいくことはできません – NOW() や RAND() のような、呼ぶたびに答えが変わる関数 — 保存したあとに検証し直せなくなるからです – ユーザー変数、ストアド関数

「この列の値は、あの表に存在すること」は CHECK では書けません。 それは外部キーの仕事です。

複数列にまたがる条件

CHECK の本領はここです。 1つの列だけでは表せないルールを書けます。

ALTER TABLE d_ct_order
  ADD CONSTRAINT ck_ct_order_discount CHECK (discount >= 0 AND discount <= qty * unit_price);

「割引額は0以上で、商品の合計金額を超えない」というルールです。

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price, discount)
VALUES (1005, 1, 1, 2, 500, 3000);
ERROR 3819 (HY000): Check constraint 'ck_ct_order_discount' is violated.

2個 × 500円 = 1000円の注文に、3000円の割引は付けられません。 これはアプリ側の1列ずつの入力チェックでは、まず漏れます。

もう1つ、日付の前後関係です。

ALTER TABLE d_ct_order
  ADD CONSTRAINT ck_ct_order_dates CHECK (ship_date <= due_date);
INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price, ship_date, due_date)
VALUES (1006, 1, 1, 1, 500, '2026-10-05', '2026-10-01');
ERROR 3819 (HY000): Check constraint 'ck_ct_order_dates' is violated.

発送日が期限より後の行は入りません。

列レベルではなく、表レベルに書く必要があります。 列の定義の横に ship_date DATE CHECK (ship_date <= due_date) と書くと、こうなります。

ERROR 3813 (HY000): Column check constraint 'xxx' references other column.

列に付ける CHECK は、その列しか見られません。 複数列を見るなら、列の定義とは別の行に CONSTRAINT ... CHECK (...) として書きます。

NULL を含むと、真偽が決まらず通る

ここが CHECK のいちばん危ない性質です。

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price, ship_date, due_date)
VALUES (1007, 1, 1, 1, 500, '2026-10-05', NULL);
Query OK, 1 row affected (0.00 sec)

通りました。 ship_date <= due_date の右側が NULL なので、比較の答えが真でも偽でもなく「不明」になります。

CHECK は「偽になった行」だけを弾きます。「不明」は通します。

小さい表で、単独で見ておきます。

DROP TABLE IF EXISTS d_ct_nullcheck;

CREATE TABLE d_ct_nullcheck (
  id  INT NOT NULL PRIMARY KEY,
  qty INT,
  CONSTRAINT ck_ct_nullcheck_qty CHECK (qty > 0)
);

INSERT INTO d_ct_nullcheck VALUES (1, 5);
INSERT INTO d_ct_nullcheck VALUES (2, -3);
INSERT INTO d_ct_nullcheck VALUES (3, NULL);
Query OK, 1 row affected (0.00 sec)
ERROR 3819 (HY000): Check constraint 'ck_ct_nullcheck_qty' is violated.
Query OK, 1 row affected (0.00 sec)
SELECT * FROM d_ct_nullcheck;
+----+------+
| id | qty  |
+----+------+
|  1 |    5 |
|  3 | NULL |
+----+------+

CHECK (qty > 0) を書いたのに、qty が NULL の行が入りました。 NULL > 0 の答えは「偽」ではなく「不明」です。 SELECTの深掘りで見た三値論理が、そのままここに出てきます。 WHERE は「真の行だけ返す」ので不明は落ちます。CHECK は「偽の行だけ弾く」ので不明は通ります。 同じ三値論理なのに、通る側が逆です。 ここを取り違えると、CHECK は守ってくれません。

直し方は2つです。

-- 方法1: NOT NULL を併記する(ふつうはこちら)
ALTER TABLE d_ct_nullcheck MODIFY qty INT NOT NULL;

いまは通りません。 NULL の行があるからです。

ERROR 1138 (22004): Invalid use of NULL value
-- 先に消してから
DELETE FROM d_ct_nullcheck WHERE qty IS NULL;
ALTER TABLE d_ct_nullcheck MODIFY qty INT NOT NULL;
-- 方法2: NULL を許したいが、値があるときは正でないと困る場合
-- CHECK (qty IS NULL OR qty > 0)  ← 意図をSQLに書き残す

方法2は、いまの CHECK (qty > 0) と結果は同じです。 それでも書く価値があります。「NULL は意図的に許している」と読めるからです。書いていないと、半年後の自分が「NULL が入っているのはバグでは」と悩みます。

同じ理由で、さっきの日付の制約は、こう書き直すほうが正直です。

ALTER TABLE d_ct_order DROP CHECK ck_ct_order_dates;

ALTER TABLE d_ct_order
  ADD CONSTRAINT ck_ct_order_dates
  CHECK (ship_date IS NULL OR due_date IS NULL OR ship_date <= due_date);

動きは変わりません。読めるようになっただけです。 ここまで来たら、1007 の行は片づけておきます。

DELETE FROM d_ct_order WHERE order_id = 1007;

⑧ 制約に名前を付ける — 付けないと直せない

ここまで、すべての制約に CONSTRAINT 名前 を書いてきました。 その理由をここで回収します。

名前を書かないとどうなるか

DROP TABLE IF EXISTS d_ct_noname;

CREATE TABLE d_ct_noname (
  id    INT NOT NULL PRIMARY KEY,
  qty   INT NOT NULL,
  price INT NOT NULL,
  CHECK (qty > 0),
  CHECK (price >= 0),
  CHECK (qty * price < 1000000)
);

INSERT INTO d_ct_noname VALUES (1, 5, 300000);
ERROR 3819 (HY000): Check constraint 'd_ct_noname_chk_3' is violated.

d_ct_noname_chk_3 と言われても、何が起きたのか分かりません。 3番目に書いた CHECK のことです。 ソースを開いて数えれば分かりますが、エラーログしか手元にない深夜には数えられません。

名前が付いていれば、こう出ます。

ERROR 3819 (HY000): Check constraint 'ck_order_total_limit' is violated.

メッセージだけで原因が分かります。 エラーの読み方の回で「メッセージを読め」と書きましたが、読めるメッセージを出すのは、書いた側の仕事です。

名前が無いと、消せない

制約を消すには、名前が要ります。

ALTER TABLE d_ct_noname DROP CHECK d_ct_noname_chk_3;

自動生成された名前を調べないと打てません。 調べる場所はここです。

SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'appdb';
+-----------------------+-------------------------------+
| CONSTRAINT_NAME       | CHECK_CLAUSE                  |
+-----------------------+-------------------------------+
| d_ct_noname_chk_1     | (`qty` > 0)                   |
| d_ct_noname_chk_2     | (`price` >= 0)                |
| ck_ct_order_qty       | (`qty` > 0)                   |
| ck_ct_member_status   | (`status` in (...))           |
+-----------------------+-------------------------------+

表全体の制約は、こちらで見えます。

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'appdb' AND TABLE_NAME = 'd_ct_member';
+------------------------+-----------------+
| CONSTRAINT_NAME        | CONSTRAINT_TYPE |
+------------------------+-----------------+
| PRIMARY                | PRIMARY KEY     |
| uq_ct_member_email     | UNIQUE          |
| uq_ct_member_dept_name | UNIQUE          |
| fk_ct_member_dept      | FOREIGN KEY     |
| ck_ct_member_name      | CHECK           |
| ck_ct_member_status    | CHECK           |
+------------------------+-----------------+

消すときのキーワードが、種類ごとに違う

ここは覚えるしかない部分です。

制約の種類消し方
CHECKALTER TABLE t DROP CHECK 名前;
UNIQUEALTER TABLE t DROP INDEX 名前; (DROP UNIQUE ではない)
外部キーALTER TABLE t DROP FOREIGN KEY 名前;
主キーALTER TABLE t DROP PRIMARY KEY; (名前を書かない)
NOT NULLALTER TABLE t MODIFY 列 型 NULL; (DROP ではない)

UNIQUE を DROP UNIQUE で消そうとして構文エラーになるのは、全員が1回やります。 UNIQUE の実体は索引なので DROP INDEX です。

名前の付け方を決めておく

この記事では、こう統一しています。

種類接頭辞例
UNIQUEuq_uq_ct_member_email
外部キーfk_fk_ct_member_dept
CHECKck_ck_ct_order_qty

種類_表名_列名や意図 の形です。 決まっていれば、エラーメッセージを見た瞬間に、どの表のどの列の話か分かります。

MySQL では、制約名はデータベースの中で一意である必要があります(表ごとではありません)。表名を名前に入れておくのは、そのためでもあります。 マイグレーションの回で Knex を使いましたが、マイグレーションでも名前は自分で書いてください。 ツールが自動生成した名前は、別の環境で違う名前になることがあります。

片づけます。

DROP TABLE IF EXISTS d_ct_noname;

⑨ すでに違反しているデータがあるとき

現実に制約を足すのは、たいてい「もう運用が始まっている表」です。 そこで必ずこうなります。

わざと汚い表を作ります。

DROP TABLE IF EXISTS d_ct_legacy;

CREATE TABLE d_ct_legacy (
  id    INT NOT NULL PRIMARY KEY,
  email VARCHAR(80),
  qty   INT
);

INSERT INTO d_ct_legacy VALUES
  (1, 'a@example.com', 3),
  (2, 'a@example.com', 5),
  (3, 'b@example.com', -1),
  (4, NULL, 2),
  (5, NULL, NULL),
  (6, 'c@example.com', 0);

制約を足そうとすると、失敗する

ALTER TABLE d_ct_legacy ADD CONSTRAINT uq_ct_legacy_email UNIQUE (email);
ERROR 1062 (23000): Duplicate entry 'a@example.com' for key 'd_ct_legacy.uq_ct_legacy_email'
ALTER TABLE d_ct_legacy ADD CONSTRAINT ck_ct_legacy_qty CHECK (qty > 0);
ERROR 3819 (HY000): Check constraint 'ck_ct_legacy_qty' is violated.

エラーは「1件目の違反」しか教えてくれません。 何件あるのか、どれなのかは言いません。 1件直して再実行 → また別の1件で止まる、を繰り返すことになります。 表が100万行あると、ALTER 1回に何分もかかります。 それを何十回もやることになります。

先に数える型

ALTER を打つ前に、SELECT で違反を全部数えます。 これが型です。

-- 1) CHECK の条件の「否定」を数える
SELECT COUNT(*) FROM d_ct_legacy WHERE NOT (qty > 0);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

2件と出ました。しかし、おかしな値が入っている行は3件あります。

SELECT id, qty FROM d_ct_legacy WHERE qty IS NULL OR NOT (qty > 0);
+----+------+
| id | qty  |
+----+------+
|  3 |   -1 |
|  5 | NULL |
|  6 |    0 |
+----+------+

WHERE NOT (qty > 0) は、qty が NULL の行を数えません。 NULL > 0 は不明、その否定も不明。WHERE は不明を落とします。 ⑦で見たとおり、CHECK (qty > 0) も NULL の行を通します。 つまりこの2件が「ALTER を止める行」としては正解です。 それでも NULL を先に見ておくのは、NOT NULL も一緒に足すつもりなら、その3件目が問題になるからです。

NULL を数えるかどうかを、毎回意識して書き分けてください。 数が合わないときは、そこに NULL がいます。

-- 2) UNIQUE の重複を数える
SELECT email, COUNT(*) AS cnt
FROM d_ct_legacy
WHERE email IS NOT NULL
GROUP BY email
HAVING cnt > 1;
+---------------+-----+
| email         | cnt |
+---------------+-----+
| a@example.com |   2 |
+---------------+-----+

WHERE email IS NOT NULL を入れているのは、NULL は重複扱いされないからです(④で見ました)。入れないと、直す必要のない行が候補に上がってきます。 GROUP BY と HAVING の使い分けは集計の深掘りで扱いました。

-- 3) 外部キーの「親がいない子」を数える型
-- SELECT COUNT(*) FROM 子 c LEFT JOIN 親 p ON c.親id = p.id
-- WHERE c.親id IS NOT NULL AND p.id IS NULL;

直してから、足す

数え終わったら、方針を決めて直します。

UPDATE d_ct_legacy SET qty = 1 WHERE qty IS NULL OR qty <= 0;
DELETE FROM d_ct_legacy WHERE id = 2;

本番でこれをやるなら、トランザクションの回でやったとおり START TRANSACTION で囲んでください。 直す UPDATE と、確かめる SELECT を同じトランザクションの中でやり、納得してから COMMIT します。 WHERE を間違えた UPDATE は、COMMIT するまでなら ROLLBACK で戻せます。

そのうえで足します。

ALTER TABLE d_ct_legacy ADD CONSTRAINT uq_ct_legacy_email UNIQUE (email);
ALTER TABLE d_ct_legacy ADD CONSTRAINT ck_ct_legacy_qty CHECK (qty > 0);
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.02 sec)

通りました。

逃げ道と、その代償

「直す時間が無いので、制約だけ先に入れたい」ときの逃げ道があります。

ALTER TABLE d_ct_legacy
  ADD CONSTRAINT ck_ct_legacy_qty2 CHECK (qty < 1000) NOT ENFORCED;

NOT ENFORCED を付けると、既存データを検査せずに定義だけ入ります。 そしてこれから入る行も検査しません。

ALTER TABLE d_ct_legacy ALTER CHECK ck_ct_legacy_qty2 ENFORCED;

有効化したときに、はじめて既存データが検査されます。

NOT ENFORCED の制約は、何も守っていません。 SHOW CREATE TABLE には出るので、「制約が付いている」と誤読されます。 そこがいちばん危ないところです。 使うなら、「いつ ENFORCED にするか」を決めてから使ってください。 決めずに入れた NOT ENFORCED は、永久に NOT ENFORCED のままです。


⑩ アプリ側の検証と、どう分担するか

「データベースに制約を書いたから、アプリのバリデーションは要らない」ではありません。 逆でもありません。役割が違います。

INSERT INTO d_ct_order (order_id, line_no, member_id, qty, unit_price) VALUES (1008, 1, 1, -1, 500);
ERROR 3819 (HY000): Check constraint 'ck_ct_order_qty' is violated.

このメッセージを、そのまま利用者の画面に出せますか。 出せません。

  • 何の項目が悪いのか書いていない(ck_ct_order_qty は開発者の言葉です)
  • どう直せばいいのか書いていない
  • 1件目でしか止まらない。 5項目間違えていても、1つずつしか教えられません
  • そもそも表の内部構造が漏れています
データベースの制約アプリの検証
役割最後の砦。 何があっても入れさせない親切な案内。 何が悪いかを日本語で返す
通る経路全部(管理画面・バッチ・手打ちSQL・別サービス)そのアプリを通ったときだけ
返せるものエラー1件、開発者向けの文言項目ごとに複数件、利用者向けの文言
できないこと他の表を見る条件(CHECK に副問い合わせは書けない)自分を通らない書き込みを止めること
書き換えられやすさALTER TABLE が要る。 事故で消えにくいリファクタで消える

実装としては、こう組みます。

  1. アプリで検証して、400 を返す。 利用者にはこれしか見えません(REST APIの回で決めたとおりの形で返します)
  2. それでも制約違反が返ってきたら、それはバグか、別経路からの書き込み。 ログに残す
  3. 利用者には「保存できませんでした」とだけ返す。 エラー番号もSQLも出さない

ただし、1つだけ「制約違反を正常系として扱う」場面があります。

try {
  await db('d_ct_member').insert({ member_name: name, email });
} catch (e) {
  if (e.errno === 1062) {
    return res.status(409).json({ error: 'このメールアドレスは登録済みです' });
  }
  throw e;
}

「先に SELECT して、無ければ INSERT」は、同時に2人が登録すると両方通ります。 SELECT と INSERT の間に、もう1人が入り込むからです。トランザクションの回で見た「在庫1個を2人が買えてしまう」のと同じ形です。 UNIQUE を張って、1062 を捕まえるのが正しい形です。 確かめてから入れるのではなく、入れてみて弾かれたら教える。 これができるのは、制約がデータベースにあるからです。


つまずいたところ

遠回りしたところを、消さずに書いておきます。

1. CHECK を書いたのに、何も起きなかった

CHECK (qty > 0) を書いた表に -5 を入れて、通りました。条件式の書き方を何度も直しました。 原因は MySQL 5.7 で、CHECK が構文としては受理され、そのまま捨てられていたからです。SELECT VERSION(); を最初に打っていれば、5分で終わっていました。 それでこの記事は①をバージョン確認から始めています。

2. NOT NULL にしたのに、名前が空の行が入った

NOT NULL を「空を禁止する」と読んでいました。空文字は値なので通ります。 CHECK (CHAR_LENGTH(TRIM(x)) > 0) を足して、やっと止まりました。

3. UNIQUE を張ったのに、同じ人が3件登録できた

メールアドレス未登録の会員が NULL で入っており、NULL は何個でも通っていました。 「UNIQUE が壊れている」と思って SHOW INDEX を何度も見ましたが、索引は正常でした。壊れていたのは私の理解です。

4. 主キーに名前を付けたのに、その名前で消せなかった

CONSTRAINT pk_xxx PRIMARY KEY (id) と書いて、DROP CONSTRAINT pk_xxx が通りませんでした。MySQL は主キーの名前を PRIMARY に固定します。 付けた名前は捨てられていました。

5. UNIQUE を DROP UNIQUE で消そうとした

構文エラーになりました。UNIQUE の実体は索引なので DROP INDEX です。 CHECK は DROP CHECK、外部キーは DROP FOREIGN KEY。3つとも違います。

6. FOREIGN_KEY_CHECKS を 1 に戻したのに、壊れた行が残っていた

「戻したのだから検査されるはず」と思っていました。されません。 戻すのは「これから先」の話だけです。LEFT JOIN ... IS NULL を自分で打つまで、壊れていることに気づけませんでした。

7. INSERT IGNORE で「エラー0件」だと思っていた

取り込んだ件数を数えていませんでした。SHOW WARNINGS を打って、切り詰められた行と捨てられた行があることに気づきました。 それ以降、IGNORE を書いたら必ず ROW_COUNT() と突き合わせるようにしています。

8. CHECK に NULL が通ることを、本番で知った

CHECK (ship_date <= due_date) を張って安心していたら、due_date が NULL の行が素通りしていました。WHERE は不明を落とし、CHECK は不明を通す。 同じ三値論理なのに向きが逆だという理解に、しばらく時間がかかりました。


後片づけ

この記事で作った表は、すべて d_ct_ で始まっています。 本線の tasks や users には触っていません。

置いたままにしても実害はありません。 それでも消したい場合は、外部キーがあるので、子から順に消します。

DROP TABLE IF EXISTS d_ct_order;
DROP TABLE IF EXISTS d_ct_member;
DROP TABLE IF EXISTS d_ct_dept;
DROP TABLE IF EXISTS d_ct_loose;
DROP TABLE IF EXISTS d_ct_legacy;
DROP TABLE IF EXISTS d_ct_nullcheck;
DROP TABLE IF EXISTS d_ct_pk;
DROP TABLE IF EXISTS d_ct_noname;
DROP TABLE IF EXISTS d_ct_code_child;
DROP TABLE IF EXISTS d_ct_code;

順番が逆だと、こう止まります。 これも外部キーが効いている証拠です。

ERROR 3730 (HY000): Cannot drop table 'd_ct_dept' referenced by a foreign key constraint

本線の表が無事なことを、必ず確かめてください。

SHOW TABLES;
SELECT COUNT(*) FROM tasks;

DROP DATABASE appdb; は打たないでください。 本線のアプリのデータが全部消えます。 消すのは表だけです。

セッションで変えた設定も戻しておきます。

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 1;

1 になっていれば正常です。 MySQL から出れば自動で戻りますが、「戻っているはず」で済ませる癖が事故のもとなので、目で見て確かめます。

EXIT;

まとめ

  • アプリの検証は「速く・親切に断る」ためのもの、制約は「絶対に入れさせない」ためのもの。 役割が違うので、両方書く
  • 入口はアプリだけではない。 管理画面・バッチ・CSV取り込み・手打ちSQL。全部を守れるのはデータベースだけ
  • NOT NULL は空を禁止しない。 NULL(値が無い状態)を禁止するだけ。空文字 '' は値なので通る
  • DEFAULT は「列を省略したとき」だけ効く。 明示的に NULL を送れば NOT NULL に弾かれる。アプリの undefined が NULL に化けるのが典型
  • UNIQUE 列に NULL は何個でも入る。 NULL 同士は「同じ」と判定できないから。1件だけにしたいなら NOT NULL も併記する
  • 複合ユニークは、制約としては列の順番で意味が変わらないが、索引としては変わる。 左から順にしか使えないので、よく検索する列を先に置く
  • ON DUPLICATE KEY UPDATE の 2 rows affected は「更新された」の意味。 どの UNIQUE に当たったかは選べない
  • INSERT IGNORE は重複だけを無視するのではない。 NOT NULL 違反・外部キー違反・文字の切り詰めまでまとめて警告に格下げする。使うなら ROW_COUNT() と SHOW WARNINGS を必ず見る
  • 主キーは NOT NULL + UNIQUE。 PRIMARY KEY を付けた列は勝手に NOT NULL になる
  • MySQL では主キーに名前を付けても PRIMARY になる。 付けた名前は捨てられる
  • InnoDB はデータを主キーの順に並べて持っている。 だから複合主キーの列の順番が、検索の速さを決める。左端の列でしか引けない
  • 複合主キーか、代理キー + UNIQUE か。 子から参照されるなら代理キーが軽い。主キーを変えると、参照している全部の表を直すことになる
  • 外部キーは型・符号・文字コード・照合順序が完全に一致しないと張れない(ERROR 3780)。SHOW CREATE TABLE を両側で見る
  • 外部キーを張ると索引が勝手に1本作られる。 同じ列に自分でもう1本張らない
  • 投入は親から、削除と DROP は子から。 順番が固定される
  • FOREIGN_KEY_CHECKS = 0 は接続の中だけ。戻し忘れると、その日の残りの作業が全部無防備になる。 そして戻しても既存の壊れた行は検査されない。LEFT JOIN ... IS NULL で自分で確かめる
  • CHECK が実際に効くのは MySQL 8.0.16 以降。 それ未満はエラーも出さずに無視する。先に SELECT VERSION();
  • CHECK は複数列にまたがる条件を書ける。 ただし列の定義の横に書くと1列しか見られない(ERROR 3813)
  • CHECK は NULL を弾かない。 NULL > 0 は偽ではなく不明で、CHECK は偽の行だけを弾く。WHERE とは通る向きが逆
  • 制約には必ず名前を付ける。 名前はエラーメッセージに出る。無いと d_ct_noname_chk_3 としか言われず、消すこともできない
  • 消し方は種類ごとに違う。 DROP CHECK / DROP INDEX / DROP FOREIGN KEY / DROP PRIMARY KEY / MODIFY 列 型 NULL
  • 既存データが違反していると ALTER は失敗し、1件目しか教えてくれない。 先に SELECT COUNT(*) で全部数える。 そのとき NULL を数えるかどうかを毎回意識する
  • NOT ENFORCED は何も守らない。 SHOW CREATE TABLE に出るぶん、付いていると誤読されるほうが危ない
  • 「先に SELECT して無ければ INSERT」は同時実行で破れる。 UNIQUE を張って、1062 を捕まえて 409 を返すのが正しい形

参考リンク


次に読む

この記事は 【深掘り】SQL の 4 / 17 本目です。

記事
← 前の記事DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない
→ 次の記事DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Constraints
  • NOT NULL / UNIQUE / 主キー / 外部キー / CHECK
  • 制約に名前を付ける・違反データの直し方

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次