CHECKと一意制約
型では防げないもの
price integer NOT NULL と書けば、価格に文字列は入りません。しかし -500 は入ります。整数だからです。
型が守ってくれるのは「どんな種類の値か」までで、「どんな範囲の値か」は守ってくれません。そこを埋めるのが CHECK 制約です。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
ALTER TABLE books
ADD CONSTRAINT books_fee_positive CHECK (price > 0);これ以降、価格に 0 以下を入れようとすると、その INSERT は失敗します。
CHECK に書けること
CHECK の中には、その行だけで判定できる条件を書けます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
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人いてはいけない、というルールを守らせます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
ALTER TABLE members
ADD CONSTRAINT members_email_uk UNIQUE (email);主キーとの違いは2つあります。UNIQUE は1つのテーブルにいくつでも張れること、そして NULL を許すことです。NULL は「値が無い」なので、複数行が NULL でも重複とは見なされません。
アプリで確かめるのでは足りない
「登録前に SELECT して、同じメールアドレスが無いか確かめている」という実装をよく見ます。これでは防げません。
2人が同時に登録した場合、どちらの SELECT も「無い」と答えたあとに、どちらも INSERT します。確かめてから入れるまでのあいだに、もう1人が入れてしまうからです。この隙間は、アプリ側の工夫では埋め切れません。
UNIQUE 制約は、データベースが挿入そのものを不可分に処理するので、隙間がありません。重複を本当に防げるのはデータベースだけです。
弾かれたことを確かめる
重複した INSERT を打つとエラーになりますが、ON CONFLICT (email) DO NOTHING を付けると、email の重複で弾かれても静かに何もしないで済みます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
INSERT INTO members VALUES (4, '別人', 'reader@example.com')
ON CONFLICT (email) DO NOTHING;制約が効いていれば、この文のあとも件数は増えません。制約が無ければ増えます。
重複を入れて確かめる
UNIQUE がある列で重複した行を入れようとすると拒否されます。ON CONFLICT (email) DO NOTHING を付けると、email の重複をエラーにせず、その行の挿入だけを見送ります。
SQL クエリ
INSERT INTO members (id, email) VALUES (9, 'reader@example.com')
ON CONFLICT (email) DO NOTHING;既に同じ email があれば行は増えません。CHECK 違反を見送る構文ではありません。演習の確認用の挿入は用意済みです。
手を動かす
2つの制約を張り、名前を付けて、本当に登録されたかを確かめます。
整合性をデータベース側で守らせます。
productsにCHECK制約products_price_positiveを足す。条件はprice > 0customersにUNIQUE制約customers_email_ukを足す。対象はemail- すでに登録済みのメールアドレスで顧客を1件入れてみる。
INSERT INTO customers VALUES (4, '別人', 'akari@example.com') ON CONFLICT DO NOTHING;を打つ。制約が効いていれば何も入りません information_schemaから、.table_constraints constraint_nameが'customers_email_uk'と'products_price_positive'の2つについて、constraint_nameとconstraint_typeをconstraint_nameの順に取り出す
重複の挿入と制約の確認は用意済みです。値の範囲とメールの一意性を守ります。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 2 行を補います。確認用の SQL は用意してあります。
テーブル構造
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 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い