ER図をDDLに落とす
変換は機械的に決まる
ここまでで、ER図の部品はひととおり出揃いました。実は、ER図から DDL への変換には判断の余地がほとんどありません。次の表のとおりに機械的に置き換えるだけです。
| ER図の要素 | DDL |
|---|---|
| エンティティ | CREATE TABLE |
| 属性 | 列 |
| 主キー | PRIMARY KEY |
| 1対多の線 | 多の側に FOREIGN KEY |
| 1対1の線 | どちらかに FOREIGN KEY + UNIQUE |
| 多対多の線 | 中間テーブル + FOREIGN KEY 2本 |
| 外部キーの列で、相手が必ずいる | NOT NULL |
| 外部キーの列で、相手がいなくてよい | NOT NULL を付けない |
判断が要るのは図を描くところまでで、そこから先は作業です。だから図をきちんと描くことに時間を使う価値があります。
記号と DDL の対応を読む
配送を追加します。線は orders ||--o| shipments と書きます。
||--o| を両方向から読みます。左から右は「1件の注文に、配送は0または1件」。右から左は「1件の配送は、ちょうど1件の注文に属する」。
これを表に当てはめると、次の3つが出てきます。
- 1対1なので、外部キーは
shipments.order_idに置き、UNIQUEを付ける - 配送は必ず注文に属するので
NOT NULL - 注文側は0を許すので、
ordersには何も足さない
「0または1」の側に列を作らない、というのがここでの読み取りです。もし orders に shipment_id を作ってしまうと、配送前の注文はすべて NULL になります。
図に書けないことは DDL にも書けない
逆に言えば、DDL に書きたいことは全部図に書いてあるはずです。もし DDL を書いていて「ここは NULL を許すんだっけ」と迷ったら、それは図が足りていないサインです。
コードを書きながら設計を決め始めると、また第1章の状態に戻ります。手が止まったら図に戻る。この往復を習慣にします。
1対1を列の定義に写す例
図書館の貸出に対し、返却記録を最大1件にする場合です。
SQL クエリ
CREATE TABLE returns (
id integer PRIMARY KEY,
loan_id integer NOT NULL UNIQUE,
returned_on date,
FOREIGN KEY (loan_id) REFERENCES borrowings (id)
);UNIQUE は同じ loan_id を2行に入れられない制約です。NOT NULL と外部キーで返却記録が必ず1つの貸出に属することを守り、UNIQUE で貸出1件あたり最大1記録にします。
手を動かす
図を DDL に変換して、変換が正しくできたかを列の定義で確かめます。
次の ER図を DDL に変換して shipments テーブルを作ってください。
erDiagram
orders ||--o| shipments : shipped_by列は次の4つです。
id integer PRIMARY KEYorder_id integer— 必ず値があり、同じ注文に2件の配送は作れない。orders (id)を参照する外部キーshipments_order_fkを付けるcarrier text— 必ず値があるshipped_on date— 出荷前は未定でよい
作ったら、確認用の SQL で shipments の列を列の名前の順に取り出します (列を書く順は問いません)。column_name、data_type、is_nullable に加えて、keys にその列に付いた主キー、一意制約、外部キー (名前と参照先の表) が出ます。一意制約と外部キーが無いと、order_id の行が正しい答えと合いません。確認用の SQL は書き換えずに最後に残してください (書き換えたかどうかは採点では見分けられません)。
id と表の外枠は用意済みです。関係の多重度と属性を残りの定義に写します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id)
);
INSERT INTO stores VALUES (1, '渋谷店');
INSERT INTO customers VALUES (1, '田中あかり');
INSERT INTO orders VALUES (1, 1, 1), (2, 1, 1);
期待される出力
| column_name | data_type | is_nullable | keys |
|---|---|---|---|
| carrier | text | NO | |
| id | integer | NO | PRIMARY KEY |
| order_id | integer | NO | shipments_order_fk REFERENCES orders / UNIQUE |
| shipped_on | date | YES |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い