ER図の記法
表と表のあいだに線を引く
第1章で5つのテーブルを作りました。しかしいまの状態では、orders の store_id に 999 を入れても誰も止めません。存在しない店舗の注文が生まれてしまいます。
エンティティ同士がどう繋がっているのかを図にしたものがER図です。E は Entity、R は Relationship の頭文字です。
mermaid で書く
ER図は手描きでも構いませんが、テキストで書けると差分が追えるので便利です。ここでは mermaid という記法を使います。
真ん中の記号が関係の形を表します。左から順に読みます。
| 記号 | 意味 |
|---|---|
| ` | |
o{ | 0以上 |
| ` | {` |
| `o | ` |
stores ||--o{ inventories は「1つの店舗に対して、在庫の行は0件以上ある」と読みます。逆から読めば「1件の在庫は、必ずちょうど1つの店舗に属する」です。
線を DDL にする
この線を実際のデータベースで表すのが外部キーです。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE branch_books (
branch_id integer NOT NULL,
book_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (branch_id, book_id),
CONSTRAINT branch_books_branch_fk FOREIGN KEY (branch_id) REFERENCES branches (id),
CONSTRAINT branch_books_book_fk FOREIGN KEY (book_id) REFERENCES books (id)
);FOREIGN KEY (store_id) REFERENCES stores (id) は「この列に値が入っているなら、その値は stores の id に必ず存在する」という約束です。NULL を止めたいときは、NOT NULL を合わせて付けます。存在しない番号を入れようとすると、データベースがその挿入を拒否します。
制約に名前を付ける
CONSTRAINT inventories_store_fk の部分は省略できます。省略するとデータベースが inventories_store_id_fkey のような名前を勝手に付けます。
しかし自分で名前を付けたほうがよいです。エラーメッセージに出るのは制約名なので、inventories_store_fk と出れば何が起きたのか一目で分かります。名前は <テーブル名>_<列の意味>_fk の形で揃えます。
図と実装は一致していないと意味がない
ER図はドキュメントなので、更新を忘れると簡単に嘘になります。だから図を描いたら必ずその場で DDL に落として動かす、という順番を習慣にします。動くものと図が同時に生まれれば、ずれようがありません。
制約の確認結果を読む
information_schema は制約の一覧です。IN は「並べた値のどれかに一致する」という条件です。
SQL クエリ
SELECT constraint_type, count(*) AS rules
FROM information_schema.table_constraints
WHERE table_name = 'borrowings'
AND constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
GROUP BY constraint_type
ORDER BY constraint_type;PRIMARY KEY と FOREIGN KEY だけに絞り、それぞれ何本あるかを数えます。演習の確認 SQL は用意済みです。
手を動かす
上の ER図をそのまま inventories の DDL にして、制約が本当に付いたかをデータベースに聞きます。
stores と products はすでにあります。次の ER図のとおりに inventories を作ってください。
erDiagram
stores ||--o{ inventories : has
products ||--o{ inventories : stocked_as列は store_id integer NOT NULL、product_id integer NOT NULL、quantity integer NOT NULL の3つ、主キーは (store_id, product_id) の複合主キーです。さらに store_id と product_id に外部キーを付けてください。
作ったら、information_schema から inventories の制約について、constraint_type が 'PRIMARY KEY' と 'FOREIGN KEY' のものだけを種類ごとに数え、constraint_type と件数を cnt という名前で constraint_type の順に取り出してください。
通常の列は用意済みです。組み合わせの主キーと、図の2本の線を制約にします。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 3 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
opened_on date
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL,
category text NOT NULL,
is_active boolean NOT NULL
);
期待される出力
| constraint_type | cnt |
|---|---|
| FOREIGN KEY | 2 |
| PRIMARY KEY | 1 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い