DDLを仕上げる
作る順番が決まっている
ここまで、テーブルは必要になった順に足してきました。しかし本番環境を新しく立てるときや、テスト用のデータベースを作り直すときには、ゼロから通しで作れる DDL 一式が要ります。
そのとき初めて、順番の問題が出てきます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE loans (
branch_id integer NOT NULL REFERENCES branches (id)
);
-- ERROR relation "branches" does not exist外部キーは、参照先のテーブルが先に存在していないと書けません。つまり DDL 一式の並び順は、参照の向きによって決まります。
依存を階層で捉える
並べ方は簡単です。誰も参照していないテーブルから始めて、参照している相手が全部そろったものを順に足していきます。
| 段 | テーブル | 参照先 |
|---|---|---|
| 1段目 | prefectures, categories, customers | 無し |
| 2段目 | stores, products | 1段目まで |
| 3段目 | product_tags, inventories, orders | 2段目まで |
| 4段目 | order_items, shipments | 3段目まで |
同じ段の中の順番はどれでも構いません。段の順番だけが決まっている、という捉え方をすると迷いません。
順番を考えたくない場合
外部キーを CREATE TABLE の中に書かず、全テーブルを作ってから ALTER TABLE ... ADD CONSTRAINT でまとめて足す書き方もあります。
| 書き方 | 利点 | 欠点 |
|---|---|---|
| CREATE の中に書く | 定義が1か所にまとまる | 作成順を守る必要がある |
| あとから ALTER で足す | 順番を気にしなくてよい | 定義が2か所に分かれる |
手で書く設計書としては前者が読みやすく、機械が出力する移行スクリプトでは後者がよく使われます。読む相手が人か機械かで選びます。
消す順番は逆になる
作り直すときは削除もします。削除の順番は作成の逆、つまり参照している側からです。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
DROP TABLE IF EXISTS loan_books;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS members;IF EXISTS を付けておくと、まだ無いテーブルを消そうとしてもエラーになりません。何度流しても同じ結果になる、という性質は移行スクリプトで重要です。
今回は定義を実行する順を選ぶ
長い列定義は用意済みの手続きにまとめてあります。CALL はその手続きを実行する命令です。手続きの中身を実装する必要はありません。
図書館の例で build_libraries が図書館表、build_borrowings が図書館を参照する貸出表を作るなら、次の順です。
SQL クエリ
CALL build_libraries();
CALL build_borrowings();逆にすると参照先が無いため作れません。演習では独立した親の作成は用意済みです。残りの手続きの参照先を見て、親から子へ呼び出してください。最後の確認では、第2章で見た1行1列の副問い合わせで表数と外部キー数を並べます。
手を動かす
1段目の2テーブルだけが用意されています。残りを正しい順番で作り切ります。
完成した定義を実行する手続き build_テーブル名 が8つ用意されています。CALL build_テーブル名(); と書くと、その表を作成できます。手続きの中身は書きません。
親の customers、stores、products と、それらを参照する inventories の呼び出しはひな形にあります。残り4つの表を、参照先が先に存在する順に作ってください。
| 手続き | 参照先 |
|---|---|
| build_shipments | orders |
| build_product_tags | products |
| build_order_items | orders、products |
| build_orders | stores、customers |
互いに参照しない表の順は交換できます。参照される表が先に存在することが条件です。
最後の確認は public のテーブル数 table_count と外部キー数 fk_count を1行2列で返します。
担当するのは残り4表の依存順です。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE prefectures (
pref text PRIMARY KEY,
region text NOT NULL
);
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE PROCEDURE build_customers() LANGUAGE SQL AS $ddl$
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
joined_on date NOT NULL
);
$ddl$;
CREATE PROCEDURE build_stores() LANGUAGE SQL AS $ddl$
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL REFERENCES prefectures (pref),
opened_on date
);
$ddl$;
CREATE PROCEDURE build_products() LANGUAGE SQL AS $ddl$
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 REFERENCES categories (id)
);
$ddl$;
CREATE PROCEDURE build_product_tags() LANGUAGE SQL AS $ddl$
CREATE TABLE product_tags (
product_id integer NOT NULL REFERENCES products (id),
tag text NOT NULL,
PRIMARY KEY (product_id, tag)
);
$ddl$;
CREATE PROCEDURE build_orders() LANGUAGE SQL AS $ddl$
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id),
ordered_on date NOT NULL
);
$ddl$;
CREATE PROCEDURE build_order_items() LANGUAGE SQL AS $ddl$
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
$ddl$;
CREATE PROCEDURE build_inventories() LANGUAGE SQL AS $ddl$
CREATE TABLE inventories (
store_id integer NOT NULL REFERENCES stores (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
PRIMARY KEY (store_id, product_id)
);
$ddl$;
CREATE PROCEDURE build_shipments() LANGUAGE SQL AS $ddl$
CREATE TABLE shipments (
id integer PRIMARY KEY,
order_id integer NOT NULL UNIQUE REFERENCES orders (id),
carrier text NOT NULL,
shipped_on date
);
$ddl$;
期待される出力
| table_count | fk_count |
|---|---|
| 10 | 10 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い