制約 (NOT NULL / UNIQUE / DEFAULT)

アプリで弾いているのに、変なデータが入る

登録フォームで「メールアドレスは必須」「重複は不可」と検査しているのに、テーブルを覗くとメールが空の行や、同じアドレスの行が 2 つある。これは珍しい話ではありません。

理由は、データの入口がフォームだけではないからです。管理画面、夜間バッチ、他システムからの取り込み、障害対応で手打ちした INSERT。入口が 5 つあれば、同じ検査を 5 か所に書いて、5 か所とも保守し続けることになります。どこか 1 つが漏れた時点で壊れたデータが入り、しかも入った後から直すのは何倍も大変です。

入口が何本あっても効く歯止めを、テーブルの側に置けます。これが制約です。

テーブル定義に書ける歯止め

まず押さえるのは 3 つです。

CREATE TABLE coupons (
  id       INT PRIMARY KEY,
  code     VARCHAR(20) NOT NULL UNIQUE,
  discount INT NOT NULL,
  expired  INT NOT NULL DEFAULT 0
);

DEFAULT には CURRENT_TIMESTAMP のような関数も書けます。作成時刻の列に入れておくと、アプリが時刻を渡し忘れても DB が埋めてくれます。

弾かれると、直す処理が要らなくなる

制約に反する書き込みは、その文が失敗します。

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 を見ます。どの列が必須で、何が重複してはいけないのか、つまりそのテーブルが守っている業務ルールが、仕様書より正確に書いてあります。