データ整合性のチェック

今日は、外部キーがどうやってデータの整合性を守ってくれるのか、そして矛盾したり間違ったデータにありがちなトラブルをどう防ぐのかを見ていくよ。

まず最初に、「データ整合性」って何を指してるのかを整理しよう。例えば注文テーブル（orders）と顧客テーブル（customers）があるとする。もし注文に紐づく顧客が顧客テーブルにいなかったら、それは整合性違反だよね。関連するテーブルのデータがちゃんと論理的に一貫してることが大事なんだ。

データ整合性ってつまり：

• 「空っぽ」の参照はなし：他のテーブルの何かを参照してるなら、その「何か」は必ず存在してること。
• 変更時の耐性：もし他のレコードが参照してる値をテーブルから消そうとしたら、DBが警告してくれたり、ちゃんと処理してくれること。

このためにPostgreSQLでは外部キーを使うんだ。

外部キーはどうやってデータ整合性を守るの？

テーブルに外部キーを作ると、PostgreSQLは自動的にこういうことをチェックしてくれる：

1. 親テーブルにデータがあるか。 レコードを挿入・更新する前に、指定した外部キーが関連テーブルにちゃんとあるかを確認する。
2. データの削除や変更。 親テーブルのレコードを消したり変えたりする前に、それを参照してる子テーブルのレコードがないかをチェックする。

外部キーはまるで「ガードマン」みたいなもの。変なデータを通さないし、テーブル同士が決められたルールの中でやりとりできるようにしてくれる。

例：学生とコースのテーブルでのデータ整合性

例えば、studentsとcoursesの2つのテーブルがあるとしよう。学生は複数のコースを受講できる。この関係を表すためにenrollmentsテーブルを使うよ。誰かが存在しないコースに学生を登録しようとした場合を考えてみよう。

ステップ1. 3つの関連テーブルを作る：

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テーブルでは、student_idとcourse_idの外部キーを明示的に指定していて、それぞれstudentsとcoursesテーブルの主キーを参照してる。

よくあるデータ整合性チェック

1. データ挿入時のチェック

もしenrollmentsテーブルに存在しないstudent_idやcourse_idでレコードを挿入しようとすると、エラーになるよ。

例：

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- エラー！ID 999の学生はいないよ。

エラーメッセージ：

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. データ削除時のチェック

親テーブルから参照されてるレコードを消そうとした場合を試してみよう。

例：

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- 挿入成功

DELETE FROM students WHERE student_id = 1; -- エラー！この学生はまだコースに登録されてる！

エラーメッセージ：

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

こういう場合に正しくレコードを消すには、CASCADEやSET NULL、RESTRICTみたいな戦略を使うんだ。これは前に話したね。

外部キーを使った整合性チェックの例

例1：自動で変なデータをブロック

外部キーのおかげで、PostgreSQLは「存在しない」データの挿入を自動で防いでくれる：

-- 存在しない学生をコースに追加しようとすると：
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- エラー！ID 42の学生はいないよ。

これで、studentsテーブルにいない学生がコースに登録されることは絶対にない。

例2：ON DELETE CASCADEでデータ削除

外部キーにON DELETE CASCADEを設定すると、親テーブルのレコードを消したときに、関連する子テーブルのデータも一緒に消えるよ。

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- 古い外部キーを削除

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- enrollmentsテーブルの該当レコードも消える

例3：ON UPDATEで変更を反映

外部キーにON UPDATE CASCADEを設定すると、親テーブルの値を変更したときに、子テーブルのデータも自動で更新される。

-- 親キーの変更が自動で子テーブルにも反映されるように外部キーを設定：
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- 学生IDを変更してみる：
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- enrollmentsテーブルのstudent_idも10に更新される。

データ整合性のテスト

外部キーの設定がいろんなケースでどう動くか、テストしてみるのはすごく大事：

1. 間違ったstudent_idやcourse_idでデータを挿入してみる。
2. studentsからデータを消して、enrollmentsテーブルがどうなるか確認する。
3. studentsテーブルのデータを変更して、関連レコードがちゃんと更新されてるかチェックする。

外部キーを使うときの注意点

たまに混乱しやすいケースもあるよ：

• インデックスがない場合。 親テーブル（例えばstudents）の参照先カラムにインデックスがないと、PostgreSQLは「頑張って」動くけど遅くなることがある。だから親テーブルの主キーは必ずインデックスにしよう。
• 循環参照。 2つのテーブルがお互いを参照し合ってると、データ挿入時にややこしくなる。こういう場合は設計を慎重に考えよう。
• 全レコード削除。 カスケード削除で全部消したいときは、子テーブルのデータの性質をちゃんと考えて、予期しない動作にならないように注意しよう。

こういう問題を避けるには、テーブル設計をしっかりやって、リレーションのルールを本番DBに適用する前にテストするのが大事だよ。