SQLでCASCADEオプションを使用して子レコードも一緒に削除する方法

この記事で扱うのは外部キーの ON DELETE CASCADE です。親行のDELETEに連動して子行を削除しますが、DROP ... CASCADEによるオブジェクト削除とは別です。例はMySQL 8.4のInnoDBを前提に説明します。PostgreSQL、SQL Server、SQLiteなどでは制約の変更方法やトリガー構文が異なります。実データではなく専用の検証用データベースで試してください。

SQLのCASCADEオプションは、データベース管理において親レコードと関連する子レコードを一括して削除するための重要な機能です。複数のテーブル間に外部キー制約がある場合、手動で子レコードを削除する手間を省き、データの整合性を維持するために役立ちます。本記事では、CASCADEオプションの設定方法と実際の使用例を詳しく説明します。

CASCADEオプションとは

CASCADEオプションとは、親レコードを削除した際に自動的に関連する子レコードも削除するSQLの機能です。これにより、手動で子レコードを削除する手間を省き、データの整合性を保つことができます。主に外部キー制約において使用されます。

CASCADEオプションのメリット

CASCADEオプションの主なメリットは以下の通りです。

自動化による効率化

親レコードを削除するだけで関連する子レコードも自動的に削除されるため、作業の効率が向上します。

データの整合性維持

親レコードと子レコードの関連性を保ちながら削除が行われるため、データの整合性を維持することができます。

コードの簡潔化

複雑なSQL文を記述する必要がなくなり、コードが簡潔になります。

CASCADEオプションの設定方法

CASCADEオプションを設定するには、外部キー制約を追加する際にCASCADEオプションを指定します。以下はその基本的な方法です。

テーブル作成時に設定

新しいテーブルを作成する際にCASCADEオプションを設定する例です。

CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

既存のテーブルに設定

以下は、その列に該当する外部キーがまだない場合の追加例です。直前のCREATE TABLE例ですでに外部キーを作った場合は重ねて実行しません。既存外部キーの動作を変更する場合は SHOW CREATE TABLE child で制約名と定義を確認し、バックアップ・孤児行の確認・影響評価を行った上で、既存制約の置換を計画します。ALTER TABLEなどのDDLは通常のDMLと同じようにはロールバックできない点にも注意してください。

ALTER TABLE child
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE;

CASCADEオプションを用いた削除例

CASCADEオプションを設定した状態で親レコードを削除すると、関連する子レコードも自動的に削除されます。以下はその具体的な例です。

データの挿入

空の検証用テーブルに、次のサンプルだけを挿入した場合の例です。同じIDの既存行がある環境では実行しないでください。

INSERT INTO parent (id, name) VALUES (1, 'Parent Record');
INSERT INTO child (id, parent_id) VALUES (1, 1);
INSERT INTO child (id, parent_id) VALUES (2, 1);

親レコードの削除

親レコードを削除すると、それに関連する子レコードも削除されます。

DELETE FROM parent WHERE id = 1;

この操作により、parentテーブルのidが1のレコードと、それに関連するchildテーブルのレコードが全て削除されます。

削除後の状態を確認

削除後にテーブルの状態を確認します。

SELECT * FROM parent;  -- 結果: 0行
SELECT * FROM child;   -- 結果: 0行

CASCADEオプションの注意点

CASCADEオプションを使用する際にはいくつかの注意点があります。これらを理解しておくことで、データベースの運用におけるトラブルを防ぐことができます。

意図しないデータ削除のリスク

CASCADEオプションを使用すると、親レコードを削除した際に関連する子レコードも自動的に削除されるため、意図せずに重要なデータが削除されるリスクがあります。慎重に設計しましょう。

トランザクション管理の重要性

大規模なデータベースでCASCADEオプションを使用する場合、トランザクション管理が重要です。一連の削除操作が途中で失敗した場合でも、一貫性を保つためにトランザクションを使用します。

パフォーマンスへの影響

大量の子行や多段の参照があると、ロック、ログ量、処理時間が増えます。外部キーの参照先だけでなく孫テーブル以降も調べ、削除対象と件数を検証してください。インデックスの要件・自動作成の有無は製品で異なります。

CASCADEオプションの使用例: 外部キー制約

外部キー制約を設定する際にCASCADEオプションを使用する具体的な例を紹介します。これにより、親子関係にあるテーブル間のデータ整合性を保ちながら削除操作を行うことができます。

外部キー制約の設定

以下は動作を示すための例です。実際の人事データで部署の削除と同時に従業員記録を消す設計を勧めるものではありません。保持義務や独立して残すべき子データがある場合はRESTRICT、NO ACTION、SET NULL、論理削除などを要件に応じて検討します。

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE CASCADE
);

データの挿入

次に、親テーブルと子テーブルにデータを挿入します。

INSERT INTO department (dept_id, dept_name) VALUES (1, 'Sales');
INSERT INTO employee (emp_id, emp_name, dept_id) VALUES (1, 'Alice', 1);
INSERT INTO employee (emp_id, emp_name, dept_id) VALUES (2, 'Bob', 1);

親レコードの削除

親テーブルのレコードを削除すると、関連する子テーブルのレコードも自動的に削除されます。

DELETE FROM department WHERE dept_id = 1;

削除後の確認

削除操作後の状態を確認します。

SELECT * FROM department;  -- 結果: 0行
SELECT * FROM employee;    -- 結果: 0行

CASCADEオプションを使わない代替方法

CASCADEオプションを使用しない場合でも、親レコードを削除する際に関連する子レコードを削除する方法があります。これには手動で削除する方法やトリガーを使用する方法があります。

手動で子レコードを削除する

親レコードを削除する前に、手動で子レコードを削除する方法です。

DELETE FROM employee WHERE dept_id = 1;
DELETE FROM department WHERE dept_id = 1;

トリガーを使用する

次はMySQLの単一文トリガーの構文例です。別の検証用構成で使う代替案であり、上のCASCADE設定へ追加する推奨手順ではありません。MySQLでは外部キーのカスケード動作はトリガーを起動しないため、監査用の子テーブルトリガーが必ず動くという設計も避けます。

CREATE TRIGGER delete_employee_before_department
BEFORE DELETE ON department
FOR EACH ROW
    DELETE FROM employee WHERE dept_id = OLD.dept_id;

トランザクションを使用する

InnoDBの検証用例です。今回はROLLBACKで戻します。本番では対象件数と全処理の成功を確認したときだけCOMMITし、途中でエラーが出たら停止してROLLBACKします。SQLクライアントによってはエラー後も後続文を続行するため、無条件にCOMMITを並べないでください。トランザクションはバックアップの代わりではありません。

START TRANSACTION;
SELECT * FROM employee WHERE dept_id = 1;
SELECT * FROM department WHERE dept_id = 1;
DELETE FROM employee WHERE dept_id = 1;
DELETE FROM department WHERE dept_id = 1;
-- 検証なので確定せず元に戻す
ROLLBACK;

これらの方法は、CASCADEオプションを使用する場合に比べて柔軟性がありますが、実装や管理が複雑になることもあります。データベースの要件に応じて適切な方法を選択してください。

まとめ

SQLのCASCADEオプションは、親レコードと関連する子レコードを一括して削除するための強力な機能です。これにより、データの整合性を保ちながら効率的に削除操作を行うことができます。本記事では、CASCADEオプションの設定方法や実際の使用例、注意点、そして代替方法について詳しく解説しました。データベース設計の際には、CASCADEオプションの利点とリスクを考慮し、最適な方法を選択することが重要です。

仕様の確認：MySQL 8.4 外部キー制約／トリガー構文。サンプルは構文と設計の説明で、利用環境のデータベースに対する実行検証はしていません。