はじめに
fjordbootcampの課題で「TwitterのER図を作成」しているのですが、自分でER図を色々と考えているうちに、外部キーというものがどのようなものなのか分からなくなってきてしまったので、概念を整理するためにまとめたいと思います。
外部キーとは
端的に言うと、「ここには、他のテーブルのデータから選んだものを入れてください。自由に入れてはダメです🙅♂️」と言うことを表すものです。
例えば
どこかのお店を想像して、顧客情報を持つCustomerテーブルと、商品情報を持つGoodsテーブルを作成したとします。
CREATE TABLE Customer ( customer_id INTEGER , customer_name VARCHAR(100), PRIMARY KEY (customer_id) ); CREATE TABLE Goods ( goods_id INTEGER , goods_name VARCHAR(100), PRIMARY KEY (goods_id) );
そして、顧客が何を購入したかを記録したいため、Purchaseテーブルを作ろうとします。
CREATE TABLE Purchase ( purchase_id INTEGER, );
ここで、Purchaseテーブルに入れる情報はcustomer_idとgoods_idになるわけですが、混乱してしまうため好き勝手に入れることはできません。
しっかりと、Customerテーブルの中のcustomer_id、Goodsテーブルの中のgoods_idを入れなければならないのです。 それを明示したのが、外部キーとというわけです。 外部キーを用いたPurchaseテーブルは以下のようになります。
CREATE TABLE Purchase ( purchase_id INTEGER , customer_id INTEGER , goods_id INTEGER , PRIMARY KEY (purchase_id) , FOREIGN KEY (customer_id) REFERENCES Customer (customer_id), FOREIGN KEY (goods_id) REFERENCES Goods (goods_id) );
では、Customer、Goodsテーブルに入っていないデータをPurchaseテーブルに入れるとどうなるのでしょうか？ 試してみます。
今回は、以下のように各テーブルにデータを入れました。いずれもIDが0003までしか存在しません。
BEGIN TRANSACTION; INSERT INTO Customer VALUES ('0001','深沢諭吉'); INSERT INTO Customer VALUES ('0002','北口英世'); INSERT INTO Customer VALUES ('0003','樋口二葉'); INSERT INTO Goods VALUES ('0001','机'); INSERT INTO Goods VALUES ('0002','椅子'); INSERT INTO Goods VALUES ('0003','テーブル'); INSERT INTO Purchase VALUES ('0001','0001','0002'); INSERT INTO Purchase VALUES ('0002','0002','0003'); INSERT INTO Purchase VALUES ('0003','0003','0001'); COMMIT;
practice=# SELECT * FROM Customer;
customer_id | customer_name
-------------+---------------
1 | 深沢諭吉
2 | 北口英世
3 | 樋口二葉
(3 rows)
practice=# SELECT * FROM Goods;
goods_id | goods_name
----------+------------
1 | 机
2 | 椅子
3 | テーブル
(3 rows)
practice=# SELECT * FROM Purchase;
purchase_id | customer_id | goods_id
-------------+-------------+----------
1 | 1 | 2
2 | 2 | 3
3 | 3 | 1
(3 rows)
ここで、以下のようにCustomerテーブル、GoodsテーブルからID0004持ってきて入れようとすると・・・
practice=# INSERT INTO Purchase VALUES ('0004','0004','0004');
ID0004はCustomerテーブルに存在しませんというエラーがでます。
ERROR: insert or update on table "purchase" violates foreign key constraint "purchase_customer_id_fkey" DETAIL: Key (customer_id)=(4) is not present in table "customer".
このように勝手に入れられないデータについて「他のテーブルの項目の範囲内から選んでデータを入れなさい」と指示するのが外部キーの役割です💪
最後に
TwitterのER図作成ですが、ER図が迷路みたいになってきて「これでいいのかな」という気持ちを持って進めています。 ですが、それなりに形はできてきているので、まずは完成を優先して、ご指導をいただきたいと思います🔥