データベースのリレーションシップとは、外部キーを使ってあるテーブルの行を別のテーブルの行に結び付けるものです。外部キーとは、関連する行の主キーを保持する列のことです。リレーションシップには一対一、一対多、多対多の3種類があり、いずれも外部キーで構築します。サンプルは PostgreSQL、MySQL、SQLite、SQL Server で動作します。
データベースのリレーションシップを作成する方法
子テーブルに親の id を格納する列を用意し、外部キーとして宣言します。これにより、実在する親の行と一致しない値はデータベースが拒否します。
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
外部キーで結合すると、関連する行をまとめて取得できます。制約により、参照先が存在しない行も防げます。
-- Input: customers
-- id | name
-- ---+-----------
-- 1 | Ana Lima
-- 2 | Ben Okafor
-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1 | 25.00
-- 11 | 2 | 15.50
-- 12 | 1 | 40.00
SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;
-- Output:
-- name | order_id | total
-- -----------+----------+------
-- Ana Lima | 10 | 25.00
-- Ben Okafor | 11 | 15.50
-- Ana Lima | 12 | 40.00
INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"
DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"
押さえておくべきルール
-
SQLite は有効にしない限り外部キーを無視します。接続のたびに
PRAGMA foreign_keys = ON;を実行してください。実行しないと、上記の不正なINSERTが通ってしまいます。 -
外部キーは上記のように独立した
FOREIGN KEY (...) REFERENCES ...行として記述します。MySQL 8.0 は列に直接書いたREFERENCES（customer_id INTEGER REFERENCES customers(id)）を黙って無視するため、不正な行がそのまま挿入されます。 -
親を削除したときの動作を決めておきます。デフォルトでは、上記のようにデータベースが削除を拒否します。外部キーに
ON DELETE CASCADEを追加すると子の行も削除され、ON DELETE SET NULLを追加すると親のない状態で子の行が残ります。 -
外部キー列にはインデックスを作成します。結合やフィルタで頻繁に使ううえ、自動でインデックスを作成するのは MySQL だけです:
CREATE INDEX idx_orders_customer_id ON orders (customer_id); -
参照先のキーと同じ型にします。たとえば
INTEGERの id にはINTEGERを使います。 -
1つの列に id のリストを格納してはいけません（例:
'1,3,5'）。データベースはそれを検証することも結合することもできません。これは多対多のリレーションシップであり、中間テーブルが必要です（後述）。
別の構文
制約に名前を付けると、エラーが読みやすくなり、後から削除するのも簡単になります。
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
既存のテーブルに外部キーを追加するには、ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id); を使います。SQLite ではこれができないため、外部キー付きの新しいテーブルを作成し、行をコピーしてからテーブルを入れ替えます。
データベースが自動で採番する id には、PostgreSQL では GENERATED ALWAYS AS IDENTITY（または SERIAL）、MySQL では AUTO_INCREMENT、SQL Server では IDENTITY(1,1)、SQLite では INTEGER PRIMARY KEY を使います。
データベースのリレーションシップはいつ使うべき？
2種類のものがつながっている場合は、必ずリレーションシップを使います。どの種類が必要かは、それぞれの側に何行存在できるかで決まります。
1つのテーブルを2つに分ける: 一対一
各行は、もう一方のテーブルの最大1行と対応します。2つ目のテーブルに UNIQUE な外部キーを追加します。列の多いテーブルから任意の列やあまり使わない列を切り出したり、機密情報（ユーザーとプロフィール、従業員と給与記録など）を別の権限で管理したりするのに使います。
1つの行に複数の子を持たせる: 一対多
最も一般的なリレーションシップです。顧客は複数の注文を持ち、著者は複数の投稿を持ちます。「多」の側に通常の外部キーを置きます。employees.manager_id が別の従業員を指す場合のように、親が同じテーブルであっても構いません。
複数の行を複数の行に結び付ける: 多対多
学生は複数のコースを受講し、コースには複数の学生がいるため、どちらのテーブルにも相手の id を持たせることはできません。両側への外部キーを持ち、その組み合わせを主キーとする中間テーブルを追加すれば、同じ関連が二重に保存されることはありません。成績など関連そのものに関する情報は中間テーブルに入れます。
相手側に対応する行がない行を見つける
リレーションシップは欠けているものも示してくれます。外部キーで LEFT JOIN すると、最初のテーブルのすべての行が残り、一致しない箇所は NULL で埋められます。そのため NULL で絞り込めば、注文のない顧客や誰も受講していないコースを見つけられます。
データベースのリレーションシップの実例
ユーザーとプロフィール
profiles.user_id は UNIQUE なので、1人のユーザーが2つ目のプロフィールを持つことはできません。LEFT JOIN により、まだプロフィールを書いていないユーザーも残ります。
-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL
-- Input: users
-- id | username
-- ---+---------
-- 1 | mia
-- 2 | raj
-- 3 | zoe
-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1 | 1 | Software developer
-- 2 | 2 | Marketing specialist
SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;
-- Output:
-- username | bio
-- ---------+---------------------
-- mia | Software developer
-- raj | Marketing specialist
-- zoe | NULL
INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"
従業員とマネージャー
1つのテーブル内の一対多のリレーションシップです。manager_id は employees.id への外部キーです。テーブルを自己結合して、各従業員とそのマネージャーを並べます。
-- Input: employees
-- id | name | manager_id
-- ---+-----------+-----------
-- 1 | Maya Chen | NULL
-- 2 | Sam Ortiz | 1
-- 3 | Jo Kim | 2
-- 4 | Lee Ross | 2
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;
-- Output:
-- employee | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim | Sam Ortiz
-- Lee Ross | Sam Ortiz
学生とコース
中間テーブル enrollments は両側への外部キーと PRIMARY KEY (student_id, course_id) を持ちます。これを介して結合し、誰が何を受講しているかを一覧にします。
-- Input: students
-- id | name
-- ---+------
-- 1 | Priya
-- 2 | Tomas
-- 3 | Leah
-- Input: courses
-- id | title
-- ---+----------------
-- 1 | Database Design
-- 2 | Web Development
-- 3 | Statistics
-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1 | 1 | A
-- 1 | 2 | B+
-- 2 | 1 | A-
-- 3 | 2 | A
SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;
-- Output:
-- student | course | grade
-- --------+-----------------+------
-- Leah | Web Development | A
-- Priya | Database Design | A
-- Priya | Web Development | B+
-- Tomas | Database Design | A-
INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"
誰も登録していないコース
NOT EXISTS も、一致しない行を見つける方法の1つです。中間テーブルに行がないコースだけが残ります。
-- Input: the courses and enrollments tables above
SELECT c.title
FROM courses c
WHERE NOT EXISTS (
SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);
-- Output:
-- title
-- ----------
-- Statistics
データベースのリレーションシップに関する参考資料
このブログの記事:
- 一対一リレーションシップガイド: 一対一を詳しく解説
- 一対多リレーションシップガイド: 子の行の結合、カウント、集計
- 多対多リレーションシップガイド: 中間テーブルを詳しく解説
- SQL JOIN 図解ガイド: リレーションシップで使うすべての結合の種類
- PostgreSQL で外部キーを使う方法: PostgreSQL の外部キーのオプション
- PostgreSQL の ON DELETE CASCADE: 親と一緒に子の行を削除する
- SQL の複合主キー: 中間テーブルで使う2列のキー
- SQL CREATE TABLE ガイド: テーブル作成時のキーと制約
公式ドキュメント:
Beekeeper Studioは無料のオープンソースのデータベースGUIです
これまで使った中で最高のSQLクエリ＆エディタツールです。データベースの管理に必要なものがすべて揃っています。 - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studioは高速で直感的、そして使いやすいツールです。多数のデータベースに対応し、Windows、Mac、Linuxで快適に動作します。
Beekeeper Studio 利用者の声
「Beekeeper Studio は以前の SQL ワークフローを完全に置き換えてくれました。高速で直感的、データベース作業がまた楽しくなりました。」
「多くのデータベース GUI を試してきましたが、Beekeeper は機能とシンプルさのバランスが完璧です。とにかくちゃんと動きます。」