つくる 制約を張り切る
守らせるものを全部データベースに移す
新しいことは出てきません。第4章で見てきた制約を、10テーブル全体に行き渡らせます。
目標ははっきりしています。
業務上あり得ないデータは、データベースが受け付けない状態にする。
いまの構造には主キーと外部キーはありますが、値の範囲を守るものが1つもありません。価格が負の商品も、個数が 0 の明細も、いまなら入ってしまいます。
張る制約の一覧
| テーブル | 制約 | 守るルール |
|---|---|---|
| products | products_price_ck | 価格は正の数 |
| order_items | order_items_qty_ck | 注文の個数は1以上 |
| inventories | inventories_qty_ck | 在庫数は0以上 |
| shipments | shipments_carrier_ck | 配送業者名は空文字ではない |
| customers | customers_email_uk | メールアドレスは重複しない |
在庫だけ0を許しているのが読みどころです。在庫が0個という状態はあり得ます。しかし注文の個数が0個というのは、注文として意味を成しません。同じ quantity という名前の列でも、意味が違えば制約も違います。名前ではなく意味で判断する、というのは正規化のときと同じです。
制約の名前を揃える
名前には規則を持たせます。ここまで使ってきた形は次のとおりです。
| 種類 | 形 | 例 |
|---|---|---|
| 外部キー | <表>_<意味>_fk | orders_store_fk |
| 一意制約 | <表>_<列>_uk | customers_email_uk |
| CHECK | <表>_<意味>_ck | products_price_ck |
名前が揃っていると、制約違反のエラーメッセージを見た瞬間に、どのテーブルの何のルールに引っかかったかが分かります。エラーは読まれる前提で設計するものです。
制約は書いた分だけ強い
最後に数えて確かめます。制約の数はそのまま「データベースが守ってくれるルールの数」です。
アプリのコードに書かれたルールは、そのコードを通った書き込みしか守れません。制約に書かれたルールは、どの入口から書き込まれても守られます。同じルールを書くなら、守備範囲の広い場所に書くほうが得です。
手を動かす
5つの制約を張って、種類ごとの本数を数えます。
テーブル構造
CREATE TABLE prefectures (
pref text PRIMARY KEY,
region text NOT NULL
);
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
opened_on date,
CONSTRAINT stores_pref_fk FOREIGN KEY (pref) REFERENCES prefectures (pref)
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL,
is_active boolean NOT NULL,
category_id integer NOT NULL,
CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES categories (id)
);
CREATE TABLE product_tags (
product_id integer NOT NULL,
tag text NOT NULL,
PRIMARY KEY (product_id, tag),
CONSTRAINT product_tags_product_fk FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
joined_on date NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL,
customer_id integer NOT NULL,
ordered_on date NOT NULL,
CONSTRAINT orders_store_fk FOREIGN KEY (store_id) REFERENCES stores (id),
CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)
);
CREATE TABLE order_items (
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id),
CONSTRAINT order_items_order_fk FOREIGN KEY (order_id) REFERENCES orders (id),
CONSTRAINT order_items_product_fk FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE inventories (
store_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (store_id, product_id),
CONSTRAINT inventories_store_fk FOREIGN KEY (store_id) REFERENCES stores (id),
CONSTRAINT inventories_product_fk FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE shipments (
id integer PRIMARY KEY,
order_id integer NOT NULL UNIQUE,
carrier text NOT NULL,
shipped_on date,
CONSTRAINT shipments_order_fk FOREIGN KEY (order_id) REFERENCES orders (id)
);
期待される出力
| constraint_type | cnt |
|---|---|
| CHECK | 4 |
| FOREIGN KEY | 10 |
| PRIMARY KEY | 10 |
| UNIQUE | 2 |