テーブル作成時の外部キーの作成

うちのコースの中でも超大事なレクチャーへようこそ！今日はPostgreSQLで外部キーをどうやって作るか話すよ。このテーマはデータベース設計でめっちゃ重要で、外部キーがあるからこそテーブル同士のつながりを作れるんだ。もし将来の「SQLシティ」で迷子になりそうなら、外部キーは色んなエリアをつなぐ橋だと思ってみて！

ざっくり言うと、外部キーってのは、あるテーブルのカラム（またはカラムのセット）が、別のテーブル（たいていは主キー）を参照するものなんだ。

例えば、students（学生）とcourses（コース）って2つのテーブルがあるとする。coursesテーブルの外部キーは「どの学生がこのコースを取ってるか」を指し示せる。こうやってテーブル同士の関係ができるんだ。

なんで大事なの？

1. 外部キーはデータの整合性を保証してくれる：他のテーブルに存在しないものを登録できなくなる。
2. データ操作が楽になる。例えば、あるテーブルのレコードを消した時に、関連するレコードも自動で消す設定ができる。

外部キー作成の構文

PostgreSQLで外部キーを作るのは簡単、ちょっとSQLマジックを使うだけ。基本の構文はこんな感じ：

CREATE TABLE zavisimaya_tablica (
    kolonka_foreign_id DATA_TYPE REFERENCES roditelskaya_tablca(kolonka_id)
);

もっと細かいところや例を見てみよう。

例1: studentsとcoursesテーブル

学生とコースをつなげたいとする。各コースは誰かの学生と紐づいてる必要がある。こんなSQLでOK：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

ここで：

• studentsテーブルではPRIMARY KEYで各学生を識別してる。
• coursesテーブルのstudent_idカラムはFOREIGN KEY（外部キー）で、studentsテーブルのstudent_idを参照してる。

studentsテーブル

coursesテーブル

重要な注意

外部キーを追加すると、PostgreSQLは自動的に「外部キーの値が指定したテーブルの既存値と一致してるか」をチェックするルールを作ってくれる。もし変な値を入れようとしたら、DBがエラーを返すよ。

実践例：students、courses、enrollmentsモデル

「多対多」関係のちょっと複雑な例を見てみよう。同じ学生が複数のコースを取れるし、1つのコースに複数の学生が参加できる。こういう関係を作るには中間テーブルが必要だよ。

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- 外部キー
    course_id INT REFERENCES courses(course_id)		-- 外部キー
);

ここでenrollmentsテーブルがstudentsとcoursesを外部キーでつないでる。

データ挿入

-- 学生を追加
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- コースを追加
INSERT INTO courses (title) VALUES ('Matematika'), ('Fizika');

-- 学生をコースに登録
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

データ取得

これで、どの学生がどのコースを取ってるか、どのコースに誰がいるか簡単にクエリできる：

-- Ivan Ivanovが取ってるコース
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- "Matematika"コースに登録してる学生
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

拡張機能：ON DELETEとON UPDATE

外部キーは、親テーブルのレコードが変更・削除された時の動作もコントロールできる。これにはON DELETEやON UPDATE修飾子を使う。主なオプションは：

• CASCADE: 親テーブルで変更や削除があったら、子テーブルにも自動で反映される。
• SET NULL: 子テーブルの外部キーの値がNULLになる。
• RESTRICT: 子テーブルで使われてる場合、親テーブルのデータ削除や変更を禁止する。
• NO ACTION: 基本的にRESTRICTと同じだけど、チェックのタイミングが遅い。

例2: ON DELETE CASCADEの使い方

例えば、studentsテーブルから学生を消したら、その学生のcoursesも自動で消したい場合はこう：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

これでstudentsから学生を消すと、その学生に紐づくcoursesのレコードも全部消える。例えば：

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Matematika', 1), ('Fizika', 1);

-- Ivanovを削除
DELETE FROM students WHERE student_id = 1;

-- Ivanovに関連するコースも全部消えてcoursesテーブルは空になる

このテーマは次のレクチャーでもっと詳しくやるよ。

外部キー利用時のデータ検証プロセス

外部キーを作ると、PostgreSQLは厳しい門番みたいに新しいレコードを全部チェックする。例えば：

• 存在しない外部キーの値でレコードを挿入しようとするとエラーになる。
• 親テーブルのレコードを消そうとして、子テーブルに関連が残ってる（ON DELETE CASCADEなし）と、整合性エラーになる。

例：不正なデータ挿入の試み

-- 存在しない学生でコース登録しようとする
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- エラー：外部キー制約違反

外部キー作成時によくあるミス

1. 外部キーにインデックスがない。PostgreSQLは主キーには自動でインデックスを作るけど、外部キーには作らない。WHERE条件で外部キーをよく使うなら、自分でインデックスを作ろう。
2. テーブル作成の順番ミス。まだ存在しないテーブルを参照する外部キーは作れない。
3. ON DELETEやON UPDATE修飾子を忘れる。これを忘れると、データ編集時に思わぬ動作になることがある。

これで外部キーの作り方がわかったら、構造化されて一貫性のあるデータベースを作る強力な武器を手に入れたってこと！次のレクチャーではON DELETE CASCADEやON UPDATE RESTRICTで関連データをどう管理するか、もっと詳しくやるよ。