DS検定のER図の読み方（1対多・多対多・主キー・外部キー）

主キーと外部キー、多対多の中間テーブル、結合時の二重計上

テーブルの中身と金額は、すべて説明用の架空の例です。

1. 主キーは行を1つに決める列で、重複もNULLも許されない。外部キーは別のテーブルの行を指す列で、値は参照先に存在しなければならない
2. 1対多は、多い側のテーブルに外部キーを置く。顧客1人に注文が多数なら、注文テーブルが顧客IDを持つ
3. 多対多は、直接つなげず、間に中間のテーブルを置いて2つの1対多に分ける。注文と商品なら、注文明細テーブルが両方のIDを持つ
4. 1対多で結合すると、1の側の行が多の側の件数ぶん増える。1の側の数値を足すと、二重に数えてしまう
5. ER図の記号は教材ごとに違う。形ではなく、線の両側が1か多かを読む

4つのテーブルと主キー・外部キー

ある店の記録を、4つのテーブルに分けて持つ例です。顧客テーブルは顧客ID・名前・年会費、商品テーブルは商品ID・名前・価格を持ちます。注文テーブルは、注文IDと顧客IDの列だけを持つ簡単な形にします。

PostgreSQLの公式文書では、主キーは行の一意な識別子として使う列で、値が重複せずNULLでもないことが求められます。外部キーは、ある列の値が別のテーブルの行に現れる値と一致しなければならないという制約で、2つのテーブルの参照整合性を保ちます。注文テーブルの顧客IDは、顧客テーブルの顧客IDを指す外部キーです。注文テーブルに「C9」の注文を入れようとしても、顧客テーブルにC9がないので登録できません。

1対多と多対多の読み方

線の両側が1なのか多なのかは、「片方の1行に、もう片方は何行つくか」を両方向から数えて決めます。顧客Aの注文は2件ですが、注文O1の顧客は1人だけです。片方向だけが多なので1対多です。

注文と商品は、注文O1に商品アと商品イの2つが入り、商品アは注文O1とO3の2件に入ります。両方向とも多なので多対多です。多対多は、どちらか一方のテーブルに外部キーの列を置いても1行に収まらず表せないので、間に中間のテーブルを置きます。PostgreSQLの公式文書も、複数の外部キーを持つテーブルで多対多の関係を実現すると説明しています。この例の注文明細テーブルが、それにあたります。

多対多は、2つの1対多に分けて読みます。注文から注文明細は1対多、商品から注文明細も1対多です。注文明細の主キーは、注文IDと商品IDを組にした2列で、同じ注文に同じ商品が2行入らないようにします。

結合すると行が増える数え方

ER図の読み方は、結合した結果の行数を数えるときに役立ちます。顧客テーブルと注文テーブルを顧客IDで左外部結合すると、顧客Aは注文が2件なので2行、顧客Bは1行、注文がない顧客Cは注文側が空の1行になり、合計4行です。顧客は3人なのに、結果は4行に増えます。

この4行で年会費を足すと、1,000円が4行ぶんで4,000円になります。実際の年会費の合計は3人で3,000円なので、顧客Aの年会費が二重に数えられています。1対多で結合すると、1の側の数値は多の側の件数ぶんだけ重複します。1の側の数値を集計するときは、結合の前に集計するか、顧客IDの重複を除いて数えます。

多対多の側から金額を出すときは、注文明細の各行で数量と価格を掛けて足します。注文O1は500円×2＋800円×1で1,800円、注文O2は800円、注文O3は500円×1＋300円×3で1,400円、全体で4,000円です。顧客別に見ると、顧客Aは注文O1とO2の合計で2,600円、顧客Bは1,400円、顧客Cは0円です。結合のしかたはSQL・データベース用語のページで確認できます。

外部キーが守る約束

外部キーがあると、参照されている行を勝手に消せません。PostgreSQLの公式文書では、参照元の行があるときに参照先の行を消したらどうするかを、ON DELETEで指定する例が載っています。商品が注文明細に残っているあいだは商品の削除を認めないRESTRICTと、注文を消したら注文明細も一緒に消すCASCADEです。

この例に当てはめると、商品アは注文明細に2行あるので、商品アの削除は拒否されます。注文O1を消すと、注文明細のO1の2行も一緒に消えます。外部キーは、存在しない相手を指す行を作らせないための約束です。どちらを選ぶかは、参照元の行が単独で意味を持つかどうかで決めます。注文明細は注文がなければ意味がないので、注文に合わせて消えるのが自然です。

よくある質問

次に読むと効率的なページ