SQL入門 STEP11｜主キーと外部キーを学ぼう
テーブル同士をつなぐ仕組みを知ろう
ここまでは、主にproductsという1つのテーブルを使ってSQLを学んできました。
現在のproductsには、商品名や価格、カテゴリーが入っています。
product_id | product_name | price | category
-----------+-----------------+--------+-----------
1 | ノートPC | 120000 | PC
2 | マウス | 3000 | 周辺機器
3 | キーボード | 8000 | 周辺機器
4 | モニター | 30000 | 周辺機器
5 | USBメモリ | 2000 | アクセサリ
6 | Webカメラ | 7000 | 周辺機器
7 | ヘッドセット | 7000 | 周辺機器
8 | HDMIケーブル 2m | 1800 | アクセサリこのくらいのデータなら、1つのテーブルでもそれほど困りません。
ですが、商品が1000件、10000件と増えていったらどうでしょう。
PCや周辺機器というカテゴリー名を、商品ごとに何度も保存することになります。
さらに、
「周辺機器という名前をPC周辺機器に変更したい」
となった場合、同じカテゴリーを使っている商品をすべて変更しなければなりません。
そこで、カテゴリーを別のテーブルに分けて管理する方法を考えてみます。
カテゴリーを別のテーブルにしてみよう
例えば、カテゴリーだけを管理するcategoriesテーブルを作ります。
イメージは次のようになります。
category_id | category_name
------------+---------------
1 | PC
2 | 周辺機器
3 | アクセサリそれぞれのカテゴリーに、category_idという番号を付けています。
そして商品のほうには、
product_id | product_name | price | category_id
-----------+-----------------+--------+------------
1 | ノートPC | 120000 | 1
2 | マウス | 3000 | 2
3 | キーボード | 8000 | 2
4 | モニター | 30000 | 2
5 | USBメモリ | 2000 | 3のように、カテゴリー名ではなくcategory_idを持たせます。
商品ID2のマウスを見ると、category_idは2です。
categoriesテーブルでID2を確認すると、
2 | 周辺機器となっています。
つまり、
マウスは「周辺機器」というカテゴリーに属している
と判断できます。
同じ「2」を使って、商品とカテゴリーがつながっているんだね♪
主キーをもう一度確認しよう
ここで、STEP2に登場したPRIMARY KEYを思い出してみましょう。
productsテーブルを作ったときは、
product_id INTEGER PRIMARY KEYとしました。
PRIMARY KEYは、日本語では主キーと呼ばれます。
主キーには、そのデータを特定するための値を指定します。
例えば、
product_id
----------
1
2
3
4
5という商品IDがあれば、
「商品IDが3の商品」
と指定するだけで、1つの商品を特定できます。
同じ商品IDが何件もあったら、どの商品なのか分からなくなってしまいます。
そのため、主キーには同じ値を重複して登録できません。
主キー（PRIMARY KEY）
テーブルの中から、1つのデータを特定するために使います。
今回なら、
products → product_id
categories → category_idを主キーとして使います。
主キーには同じ値を重複して登録できません。
categoriesテーブルを作ろう
それでは実際にcategoriesテーブルを作ります。
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY,
category_name VARCHAR(50)
);ここでもcategory_idにPRIMARY KEYを設定しています。
作成できたら、カテゴリーを登録しましょう。
INSERT INTO categories (category_id, category_name)
VALUES
(1, 'PC'),
(2, '周辺機器'),
(3, 'アクセサリ');登録できたかSELECTで確認します。
SELECT *
FROM categories
ORDER BY category_id ASC;次の3件が表示されればOKです。
category_id | category_name
------------+---------------
1 | PC
2 | 周辺機器
3 | アクセサリ外部キーとは？
ここからが今回の新しい内容です。
productsとcategoriesをつなぐために使うのが外部キー（FOREIGN KEY）です。
先ほどの商品データでは、
product_id | product_name | category_id
-----------+--------------+------------
1 | ノートPC | 1
2 | マウス | 2となります。
このように、別のテーブルにあるデータとの関係を持たせるために使うカラムが外部キーです。
外部キー（FOREIGN KEY）
別のテーブルにあるデータとの関係を持たせるために使います。
今回なら、
products.category_id
↓
categories.category_idという関係を作ります。
商品が「どのカテゴリーに属しているのか」を、category_idを使って表せるようになります。
productsにcategory_idを追加しよう
現在のproductsには、STEP10で作ったcategoryがあります。
今回はテーブル同士をつなぐため、新しくcategory_idを追加します。
ALTER TABLE products
ADD COLUMN category_id INTEGER;これで、
product_id
product_name
price
category
category_idという状態になります。
まだcategory_idには値が入っていません。
そこで、商品ごとにカテゴリーIDを設定します。
商品にカテゴリーIDを設定しよう
STEP10で使ったUPDATEをもう一度使います。
UPDATE products
SET category_id = 1
WHERE product_id = 1;
UPDATE products
SET category_id = 2
WHERE product_id IN (2, 3, 4, 6, 7);
UPDATE products
SET category_id = 3
WHERE product_id IN (5, 8);これで、
- ノートPC →
1（PC） - マウスなど →
2（周辺機器） - USBメモリなど →
3（アクセサリ）
という関係ができました。
確認してみましょう。
SELECT
product_id,
product_name,
category_id
FROM products
ORDER BY product_id ASC;product_id | product_name | category_id
-----------+-----------------+------------
1 | ノートPC | 1
2 | マウス | 2
3 | キーボード | 2
4 | モニター | 2
5 | USBメモリ | 3
6 | Webカメラ | 2
7 | ヘッドセット | 2
8 | HDMIケーブル 2m | 3外部キーを設定しよう
category_idを追加しただけでも、番号を使ってカテゴリーとの関係を表すことはできます。
ただし、今の状態では存在しないカテゴリーIDを登録することもできてしまいます。
そこでFOREIGN KEYを設定します。
ALTER TABLE products
ADD CONSTRAINT fk_products_category
FOREIGN KEY (category_id)
REFERENCES categories(category_id);少し長くなりましたね。
今回は全部を暗記する必要はありません。
特に見てほしいのはここです。
FOREIGN KEY (category_id)
REFERENCES categories(category_id)これは、
productsのcategory_idは、categoriesのcategory_idを参照します
という指定です。
ADD CONSTRAINT fk_products_categoryの部分は、この制約にfk_products_categoryという名前を付けています。
長く見えるけど、「どのカラムが、どこを参照するの？」を見ると分かりやすいね♪
存在しないカテゴリーを設定するとどうなる？
外部キーを設定したことで、products.category_idには何でも好きな番号を入れられるわけではなくなりました。
現在、categoriesにあるIDは、
1
2
3です。
では、存在しない99を設定しようとするとどうなるでしょうか。
次のSQLは、エラーを確認するためのものです。
UPDATE products
SET category_id = 99
WHERE product_id = 1;実行すると、外部キーの制約に違反するためエラーになります。
categoriesにID99が存在しないからです。
エラーになったので、商品ID1のcategory_idは変更されません。
外部キーが間違った関係を防いでくれる
例えば、
商品 → category_id = 99となっているのに、カテゴリーID99がどこにも存在しなければ、どのカテゴリーの商品なのか分かりません。
FOREIGN KEYを設定しておけば、このような存在しないカテゴリーとの関係を防ぐことができます。
主キーと外部キーの違いを整理しよう
ここは最初のうちは混ざりやすいので、一度整理しておきます。
| 種類 | 今回の例 | 役割 |
|---|---|---|
| 主キー | categories.category_id | カテゴリーを特定する |
| 外部キー | products.category_id | カテゴリーを参照する |
例えば、
categories
category_id = 2 ← 主キー
↑
│ 参照
│
products
category_id = 2 ← 外部キーという関係です。
categories側の2は、
「カテゴリーID 2のデータを特定する番号」
です。
products側の2は、
「カテゴリーID 2を参照しています」
という意味になります。
同じcategory_idという名前でも、テーブルによって役割が違うところに注意してください。
なぜカテゴリー名ではなくIDでつなぐの？
ここで、
「最初からcategoryに『周辺機器』と入っていたのだから、それでよくない？」
と思うかもしれません。
小さなデータなら、それでも扱えます。
ですが、例えば1000個の商品がすべて、
周辺機器という文字を直接持っていたとします。
後からカテゴリー名を、
周辺機器
↓
PC周辺機器に変える場合、多くの商品データを変更することになります。
カテゴリーを別テーブルにしてIDでつないでおけば、カテゴリー名そのものはcategories側で管理できます。
商品のほうは、
category_id = 2という関係を持っているだけです。
こうして役割ごとにテーブルを分けて管理できるのが、リレーショナルデータベースの特徴の一つです。
ここでは難しい設計ルールまで覚えなくて大丈夫です。
「同じ情報を何度も持たせるのではなく、別テーブルに分けてIDでつなぐことがある」
くらいの理解から始めましょう。
STEP10のcategoryはどうする？
現在のproductsには、
category
category_idの両方があります。
STEP10ではGROUP BYの練習をするために、カテゴリー名をproductsへ直接追加しました。
これからはcategoriesテーブルとcategory_idを使うので、元のcategoryは不要になります。
削除しておきましょう。
ALTER TABLE products
DROP COLUMN category;これでproductsは、
product_id
product_name
price
category_idという構成になります。
最後に2つのテーブルを確認しよう
まずproductsです。
SELECT *
FROM products
ORDER BY product_id ASC;product_id | product_name | price | category_id
-----------+-----------------+--------+------------
1 | ノートPC | 120000 | 1
2 | マウス | 3000 | 2
3 | キーボード | 8000 | 2
4 | モニター | 30000 | 2
5 | USBメモリ | 2000 | 3
6 | Webカメラ | 7000 | 2
7 | ヘッドセット | 7000 | 2
8 | HDMIケーブル 2m | 1800 | 3次にcategoriesです。
SELECT *
FROM categories
ORDER BY category_id ASC;category_id | category_name
------------+---------------
1 | PC
2 | 周辺機器
3 | アクセサリこの2つのテーブルは、category_idで関係しています。
ところが、今のSELECTではそれぞれ別々に表示しています。
できれば、
ノートPC | 120000 | PC
マウス | 3000 | 周辺機器
USBメモリ | 2000 | アクセサリのように、商品とカテゴリー名を一緒に表示したいですよね。
そこで次に使うのがJOINです。
よくある間違い
主キーと外部キーを同じものだと思ってしまう
どちらにもcategory_idを使っているので、最初は混同しやすいところです。
categories.category_idは、カテゴリーそのものを特定する主キー。
products.category_idは、そのカテゴリーを参照する外部キーです。
役割で分けて考えてみましょう。
存在しないIDを外部キーに設定する
categoriesに、
1
2
3しかない状態で、products.category_idへ99を設定することはできません。
FOREIGN KEYが、存在しないカテゴリーとの関係を防いでくれます。
IDだけ見て意味がないと思ってしまう
productsだけを見ると、
category_id = 2では、何のカテゴリーなのか分かりにくいですね。
ですがcategoriesを見ると、
2 | 周辺機器と分かります。
この2つのテーブルをまとめて取得する方法を、次のSTEPで学びます。
STEP11のまとめ
STEP11のポイント
- データは役割ごとに複数のテーブルへ分けて管理することがある
PRIMARY KEYは1つのデータを特定するための主キー- 主キーには同じ値を重複して登録できない
FOREIGN KEYは別のテーブルとの関係を持たせるための外部キーREFERENCESで参照するテーブルとカラムを指定する- 外部キーを設定すると、存在しないデータとの関係を防げる
- 今回は
category_idを使ってproductsとcategoriesをつないだ
今回は、これまでのように「データを取得するSQL」よりも、テーブル同士の関係について考える内容が中心でした。
主キーと外部キーは、名前だけで覚えようとすると分かりにくいかもしれません。
そんなときは、
主キー → そのデータを特定する
外部キー → 別のテーブルのデータを参照する
と考えてみてください。
そして今回作った、
products.category_id
↓
categories.category_idという関係は、このまま次のSTEPで使います。
STEP11クリア！
2つのテーブルがcategory_idでつながったね。次はいよいよ、この2つを一緒に見てみよう♪
次のSTEPへ
productsには商品名や価格があり、categoriesにはカテゴリー名があります。
今は別々にSELECTしないと、それぞれの内容を見ることができません。
次はINNER JOINを使って2つのテーブルを結合し、
「商品名・価格・カテゴリー名」
を1回のSELECTで取得してみます。
ここまで作ってきた主キーと外部キーが、実際にどう使われるのかが見えてきます。
→ STEP12「INNER JOINでテーブルを結合しよう」
学習内容を確認したいときは、SQL入門の一覧から各STEPを確認できます。