CHECKと一意制約
型では防げないもの
price integer NOT NULL と書けば、価格に文字列は入りません。しかし -500 は入ります。整数だからです。
型が守ってくれるのは「どんな種類の値か」までで、「どんな範囲の値か」は守ってくれません。そこを埋めるのが CHECK 制約です。
ALTER TABLE products
ADD CONSTRAINT products_price_positive CHECK (price > 0);これ以降、価格に 0 以下を入れようとすると、その INSERT は失敗します。
CHECK に書けること
CHECK の中には、その行だけで判定できる条件を書けます。
CHECK (quantity > 0)
CHECK (is_active IN (true, false))
CHECK (shipped_on >= ordered_on)
CHECK (char_length(code) = 6)3つ目のように同じ行の別の列を参照できるのが便利なところです。出荷日が注文日より前、という物理的にあり得ない行を止められます。
書けないのは、他の行や他のテーブルを見ないと判定できない条件です。「合計金額が明細の合計と一致すること」は CHECK では書けません。
UNIQUE は重複を防ぐ
もう1つが UNIQUE です。メールアドレスが同じ顧客が2人いてはいけない、というルールを守らせます。
ALTER TABLE customers
ADD CONSTRAINT customers_email_uk UNIQUE (email);主キーとの違いは2つあります。UNIQUE は1つのテーブルにいくつでも張れること、そして NULL を許すことです。NULL は「値が無い」なので、複数行が NULL でも重複とは見なされません。
アプリで確かめるのでは足りない
「登録前に SELECT して、同じメールアドレスが無いか確かめている」という実装をよく見ます。これでは防げません。
2人が同時に登録した場合、どちらの SELECT も「無い」と答えたあとに、どちらも INSERT します。確かめてから入れるまでのあいだに、もう1人が入れてしまうからです。この隙間は、アプリ側の工夫では埋め切れません。
UNIQUE 制約は、データベースが挿入そのものを不可分に処理するので、隙間がありません。重複を本当に防げるのはデータベースだけです。
弾かれたことを確かめる
重複した INSERT を打つとエラーになりますが、ON CONFLICT DO NOTHING を付けると、弾かれても静かに何もしないで済みます。
INSERT INTO customers VALUES (4, '別人', 'akari@example.com')
ON CONFLICT DO NOTHING;制約が効いていれば、この文のあとも件数は増えません。制約が無ければ増えます。
手を動かす
2つの制約を張り、名前を付けて、本当に登録されたかを確かめます。
テーブル構造
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL
);
INSERT INTO products VALUES
(1, 'ノートPC', 128000),
(2, 'マウス', 3200);
INSERT INTO customers VALUES
(1, '田中あかり', 'akari@example.com'),
(2, '佐藤けんじ', 'kenji@example.com'),
(3, '鈴木みか', 'mika@example.com');
期待される出力
| constraint_name | constraint_type |
|---|---|
| customers_email_uk | UNIQUE |
| products_price_positive | CHECK |