外部キーを考慮したデータの削除と変更

さて、もう外部キー（FOREIGN KEY）が何か、どう動くか、テーブル作成で使う練習もしたよね。でも、いざデータを消したり、関連レコードを変更したくなったらどうする？今回は、外部キーと一緒にデータ削除・変更がどう動くか、どんなトリックがあるか見ていこう！

データベースって、複雑なトランプタワーみたいなものだと思ってみて。一枚抜いたら全体が崩れるかも。そこで外部キーの出番！関連データを消すときに「崩壊」を防いでくれるんだ。どうやって動くか、一緒に見てみよう。

データを削除しようとしたらどうなる？

テーブルに外部キーがあると、DBMSはそのレコードが他のテーブルと関連してるかチェックする。もし関連してたら、削除しようとするとデータ整合性エラーになることも。そんなサプライズを防ぐために、外部キーの動作をあらかじめ決めておけるよ。これはON DELETEオプションで設定するんだ。

ON DELETEで動作を設定する

外部キーを作るとき、次のどれかのルールを指定できるよ：

1. ON DELETE CASCADE: 親テーブルのレコードを消すと、自動的に子テーブルの関連レコードも全部消える。
2. ON DELETE SET NULL: 関連レコードは消さず、外部キーの値をNULLにする。
3. ON DELETE SET DEFAULT: 外部キーの値をデフォルト値にする。
4. ON DELETE RESTRICT（デフォルト動作）: 関連レコードがあると削除できず、エラーになる。
5. ON DELETE NO ACTION: ほぼRESTRICTと同じだけど、整合性チェックがトランザクション終了まで遅延される。

例：カスケード削除 ON DELETE CASCADE

-- クライアントテーブル
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 注文テーブル
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- データ挿入
INSERT INTO customers (name) VALUES ('イワン イワノフ');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- クライアント削除
DELETE FROM customers WHERE customer_id = 1;

-- ordersテーブルの状態を確認
SELECT * FROM orders; -- レコードなし、全部カスケードで消えた！

customersテーブルからレコードを消すと、PostgreSQLは自動でそのクライアントの注文もordersテーブルから消してくれる。

例：リンクをNULLにする ON DELETE SET NULL

-- 新しい動作ルールの注文テーブル
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- データ挿入
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- クライアント削除
DELETE FROM customers WHERE customer_id = 1;

-- orders_with_nullテーブルを確認
SELECT * FROM orders_with_null;

結果：

ON DELETE SET NULLを使うと、注文は残るけど、もう存在しないクライアントとの紐付けが外れる感じになる。

外部キーを考慮したデータの変更

削除だけじゃなくて、親テーブルのデータを変更したときも関連レコードに影響が出ることがある。例えば、クライアントのcustomer_idを変えたらどうなる？ここでON UPDATEオプションが活躍するよ。

ON UPDATEで動作を設定する

親テーブルの変更に対して、次の戦略で対応できる：

1. ON UPDATE CASCADE: 親テーブルの外部キー値を変えると、子テーブルの関連レコードも自動で更新される。
2. ON UPDATE SET NULL: 子テーブルの外部キー値をNULLにする。
3. ON UPDATE SET DEFAULT: デフォルト値にする。
4. ON UPDATE RESTRICT: 関連レコードがあると外部キー値の変更は禁止。
5. ON UPDATE NO ACTION: チェックはトランザクション終了まで遅延。

例：カスケード更新 ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- データ挿入
INSERT INTO customers_with_cascade (name) VALUES ('イワン イワノフ');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- customer_idを変更
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- orders_with_cascadeテーブルを確認
SELECT * FROM orders_with_cascade;

結果：

customer_idを変更すると、PostgreSQLは自動でorders_with_cascadeテーブルの値も更新してくれる。

実践：enrollmentsテーブル

前にやったstudents（学生）とcourses（コース）の例を思い出してみて。今回はコース登録テーブルenrollmentsで、データ削除の管理を設定してみよう。

-- 学生テーブル
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 (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- データ挿入
INSERT INTO students (name) VALUES ('アレクセイ ペトロフ');
INSERT INTO courses (title) VALUES ('マテマティカ');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- 学生削除
DELETE FROM students WHERE student_id = 1;

-- enrollmentsテーブルを確認
SELECT * FROM enrollments; -- 空っぽ！レコードは自動で消えた。

よくあるミスとその防止法

よくあるミスの一つは、親テーブルからレコードを消そうとして、外部キーの動作をちゃんと設定してないこと。例えばON DELETEを何も指定しないと、デフォルトでRESTRICTになってエラーになるよ。

あと、カスケード操作（CASCADE）を多用しすぎると、思ったより多くのデータをうっかり消しちゃうこともあるから注意！

こういう問題を避けるために、こんなポイントを意識しよう：

• ON DELETEやON UPDATEの動作は、アプリのロジックに合わせてちゃんと考えて設定しよう。
• 大事な操作の前には、確認用のSELECT文を入れてから変更や削除を実行しよう。
• トランザクションを使って、もしミスったらロールバックできるようにしよう。