制約 (NOT NULL / UNIQUE / DEFAULT)
アプリで弾いているのに、変なデータが入る
登録フォームで「メールアドレスは必須」「重複は不可」と検査しているのに、テーブルを覗くとメールが空の行や、同じアドレスの行が 2 つある。これは珍しい話ではありません。
理由は、データの入口がフォームだけではないからです。管理画面、夜間バッチ、他システムからの取り込み、障害対応で手打ちした INSERT。入口が 5 つあれば、同じ検査を 5 か所に書いて、5 か所とも保守し続けることになります。どこか 1 つが漏れた時点で壊れたデータが入り、しかも入った後から直すのは何倍も大変です。
入口が何本あっても効く歯止めを、テーブルの側に置けます。これが制約です。
テーブル定義に書ける歯止め
まず押さえるのは 3 つです。
SQL クエリ
CREATE TABLE coupons (
id INT PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
discount INT NOT NULL,
expired INT NOT NULL DEFAULT 0
);| 制約 | 効き目 | これが無いと起きること |
|---|---|---|
NOT NULL | 空の値を拒否する | 割引額が未設定の行が計算式を壊す |
UNIQUE | 同じ値の行を拒否する | 同じクーポンコードが 2 枚発行される |
DEFAULT | 省略されたときの値を決める | 指定を忘れた行だけ NULL になって判定できない |
DEFAULT には CURRENT_TIMESTAMP のような関数も書けます。作成時刻の列に入れておくと、アプリが時刻を渡し忘れても DB が埋めてくれます。
弾かれると、直す処理が要らなくなる
制約に反する書き込みは、その文が失敗します。
SQL クエリ
INSERT INTO coupons (id, code, discount) VALUES (1, 'SPRING10', 100);
INSERT INTO coupons (id, code, discount) VALUES (2, 'SPRING10', 200);
-- 2 行目が UNIQUE 違反で失敗する。1 行目は入ったまま大事なのは、速く気づけることより「入ってから直す」仕事が消えることです。壊れたデータを探すクエリも、修復スクリプトも、その結果を確かめる手順も、そもそも要らなくなります。アプリ側の検査は使い勝手のため（画面に赤字を出すため）に残しますが、最後に間違いを止めるのは DB です。
ただし後から制約を足すときは順番があります。すでに NULL が入っている列に NOT NULL を足そうとしても、その ALTER TABLE は失敗します。前回の 3 段階と同じで、先に UPDATE で埋めてから締めます。
知らないテーブルを渡されたら、まず定義の
NOT NULLとUNIQUEを見ます。どの列が必須で、何が重複してはいけないのか、つまりそのテーブルが守っている業務ルールが、仕様書より正確に書いてあります。
テーブル構造
SELECT 1;期待される出力
| id | name | is_active | |
|---|---|---|---|
| 1 | a@example.com | 田中 | 1 |
| 2 | b@example.com | 佐藤 | 0 |
| 3 | c@example.com | 山田 | 1 |