アプリで弾いたはずの値が入ります。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違反も外部キー違反も文字切り詰めも、まとめて握り潰して先へ進む- 制約に名前を付けないと、エラーメッセージから原因にたどり着けないこと。そして名前が無いと、消すこともできない
この記事でやること
- MySQL のバージョンを先に確かめる(
CHECKが実際に効くのは 8.0.16 以降。それ未満はエラーも出さずに無視する) - 制約を1つも付けない表を作り、ゴミを4種類わざと入れる。 何が入るのかを先に目で見る
- 同じ表を、制約を付けて作り直す。 さっきと同じ
INSERTが、4つとも別々のエラーで止まることを確かめる UNIQUEにNULLを何個も入れる。 複合ユニークの列の順番をEXPLAINで確かめ、ON DUPLICATE KEY UPDATEとINSERT IGNOREを打ち分けるCHECKで負の数量を弾き、複数列にまたがる条件を書き、NULLを混ぜて素通りさせる- すでに違反しているデータが入っている表に、あとから制約を足して失敗させる。 先に違反行を数える型を身につける
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 制約(せいやく / 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 に入ります。
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 |
+------------------------+-----------------+
消すときのキーワードが、種類ごとに違う
ここは覚えるしかない部分です。
| 制約の種類 | 消し方 |
|---|---|
CHECK | ALTER TABLE t DROP CHECK 名前; |
UNIQUE | ALTER TABLE t DROP INDEX 名前; (DROP UNIQUE ではない) |
| 外部キー | ALTER TABLE t DROP FOREIGN KEY 名前; |
| 主キー | ALTER TABLE t DROP PRIMARY KEY; (名前を書かない) |
NOT NULL | ALTER TABLE t MODIFY 列 型 NULL; (DROP ではない) |
UNIQUE を DROP UNIQUE で消そうとして構文エラーになるのは、全員が1回やります。 UNIQUE の実体は索引なので DROP INDEX です。
名前の付け方を決めておく
この記事では、こう統一しています。
| 種類 | 接頭辞 | 例 |
|---|---|---|
UNIQUE | uq_ | uq_ct_member_email |
| 外部キー | fk_ | fk_ct_member_dept |
CHECK | ck_ | 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万行あると、
ALTER1回に何分もかかります。 それを何十回もやることになります。
先に数える型
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 が要る。 事故で消えにくい | リファクタで消える |
実装としては、こう組みます。
- アプリで検証して、400 を返す。 利用者にはこれしか見えません(REST APIの回で決めたとおりの形で返します)
- それでも制約違反が返ってきたら、それはバグか、別経路からの書き込み。 ログに残す
- 利用者には「保存できませんでした」とだけ返す。 エラー番号も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 を返すのが正しい形
参考リンク
- roadmap.sh — SQL
- MySQL 8.0 リファレンスマニュアル(日本語)
- MySQL — CREATE TABLE 構文
- MySQL — CHECK 制約
- MySQL — FOREIGN KEY 制約
- MySQL — ALTER TABLE 構文
- MySQL — INSERT … ON DUPLICATE KEY UPDATE
- MySQL — サーバーSQLモード
- MySQL — サーバーシステム変数(foreign_key_checks)
- MySQL — NULL 値でよくある問題
- MySQL — InnoDB のクラスタ化索引とセカンダリ索引
- MySQL — サーバーエラーメッセージ一覧
次に読む
この記事は 【深掘り】SQL の 4 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない |
| → 次の記事 | DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- NOT NULL / UNIQUE / 主キー / 外部キー / CHECK
- 制約に名前を付ける・違反データの直し方
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント