MySQL 外部キー制約とカスケード削除：サンプルコードと解説

2024-05-21

MySQLにおける外部キー制約とカスケード削除：詳細ガイド

本記事では、MySQLにおける外部キー制約とカスケード削除について、詳細な解説を行います。具体的な構文例、動作の仕組み、注意点などを分かりやすく説明していきますので、ぜひ参考にしてください。

外部キー制約とは？

外部キー制約は、あるテーブル（子テーブル）の列を、別のテーブル（親テーブル）の列を参照するように設定することで、両テーブル間の関連性を定義するものです。これにより、子テーブルのデータが常に親テーブルの有効なデータを参照していることを保証します。

外部キー制約を作成するには、以下の構文を使用します。

ALTER TABLE 子テーブル ADD FOREIGN KEY (子テーブル_列)
REFERENCES 親テーブル(親テーブル_列)
[ON DELETE 参照アクション]
[ON UPDATE 参照アクション];

子テーブル: 外部キー制約を設定するテーブル
子テーブル_列: 外部キーとなる列
親テーブル: 参照されるテーブル
ON DELETE 参照アクション: 親テーブルのレコード削除時の動作を指定 (デフォルトはRESTRICT)
- CASCADE: 親レコード削除時に関連する子レコードを自動的に削除
- RESTRICT: 親レコードが存在しない子レコードの削除を禁止
- SET NULL: 親レコード削除時に関連する子レコードの外部キー列をNULLに設定

カスケード削除は、親テーブルのレコード削除時に、関連する子テーブルのレコードを自動的に削除する機能です。これは、ON DELETE CASCADE句を外部キー制約に設定することで有効化されます。

カスケード削除が有効な場合、親テーブルからレコードが削除されると、そのレコードを参照しているすべての子テーブルのレコードも自動的に削除されます。これは、データの整合性を保ち、参照エラーを防ぐのに役立ちます。

例:

ordersテーブルとorder_itemsテーブルがあると仮定します。ordersテーブルには注文情報が格納され、order_itemsテーブルには注文明細情報が格納されます。order_itemsテーブルには、order_idという外部キー列があり、ordersテーブルのorder_id列を参照します。

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  order_date DATE
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
  ON DELETE CASCADE
);

上記の例では、ordersテーブルから注文レコード（order_id = 101）を削除すると、order_itemsテーブルからその注文に関連するすべての注文明細レコードも自動的に削除されます。

カスケード削除は便利な機能ですが、誤った使用には注意が必要です。意図せずに子レコードを削除してしまう可能性があるため、使用する前に十分に検討する必要があります。

カスケード削除を使用する際の注意点:

親レコードと子レコードの関係を十分に理解していることを確認してください。
カスケード削除を使用する前に、データベースのバックアップを取ることをお勧めします。
誤削除のリスクを軽減するために、論理削除を実装することを検討してください。

Innodbとカスケード削除

InnoDBは、MySQLで使用される一般的なストレージエンジンです。InnoDBでは、カスケード削除がデフォルトで有効になっています。ただし、以下の設定で無効にすることもできます。

MySQLにおける外部キー制約とカスケード削除：サンプルコード

サンプルデータベース

テーブル構成:

customersテーブル: 顧客情報
- customer_id (INT, PRIMARY KEY, AUTO_INCREMENT): 顧客ID
- first_name (VARCHAR(50)): 名
- email (VARCHAR(100)): メールアドレス
ordersテーブル: 注文情報
- customer_id (INT): 顧客ID (FOREIGN KEY references customers(customer_id))
- order_date (DATE): 注文日
- order_status (VARCHAR(20)): 注文ステータス
- order_id (INT): 注文ID (FOREIGN KEY references orders(order_id) ON DELETE CASCADE)
- product_id (INT): 商品ID
- quantity (INT): 数量
- unit_price (DECIMAL(10,2)): 単価

サンプルクエリ

CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_status VARCHAR(20) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

データ挿入

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Smith', '[email protected]');

INSERT INTO orders (customer_id, order_date, order_status)
VALUES (1, '2024-05-21', 'Pending');

INSERT INTO orders (customer_id, order_date, order_status)
VALUES (2, '2024-05-21', 'Processing');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 123, 1, 10.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 456, 2, 20.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (2, 789, 3, 30.00);

カスケード削除

DELETE FROM orders
WHERE order_id = 1;

結果確認

以下のクエリを実行して、各テーブルのレコードを確認できます。

SELECT * FROM customers;

SELECT * FROM orders;

SELECT * FROM order_items;

出力結果:

+-------------+----------+---------+-----------------+
| customer_id | first_name | last_name | email           |
+-------------+----------+---------+-----------------+
| 1           | John      | Doe      | [email protected] |
| 2           | Jane      | Smith    | [email protected] |
+

MySQLにおける外部キー制約とカスケード削除の代替方法

そこで、カスケード削除の代替方法として以下のアプローチが考えられます。

トリガーを使用する**

トリガーは、データベース操作に応じて自動的に実行されるプログラムです。トリガーを使用することで、親レコード削除時に関連する子レコードを削除する処理を独自に実装することができます。

CREATE TRIGGER delete_order_items_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  DELETE FROM order_items WHERE order_id = OLD.order_id;
END;

上記のトリガーは、ordersテーブルからレコードが削除される前に実行され、そのレコードに関連するorder_itemsテーブルのレコードをすべて削除します。

トリガーを使用する利点は、以下の通りです。

カスケード削除よりも柔軟な処理が可能
削除処理の前に追加の処理を実行できる
監査ログ記録などの目的に利用できる

複雑なロジックになると、トリガーの記述が煩雑になる
トリガーの実行によってパフォーマンスが低下する可能性がある

論理削除を使用する**

論理削除とは、レコードを物理的に削除する代わりに、削除フラグを立てることで論理的に削除状態にする手法です。論理削除を使用することで、意図せずに削除してしまったレコードを復元することが可能になります。

UPDATE orders
SET is_deleted = 1
WHERE order_id = 1;

上記のクエリを実行すると、ordersテーブルのorder_id = 1のレコードは論理的に削除されます。論理削除されたレコードは、is_deleted列の値が1になっていることで識別できます。

誤削除のリスクを軽減できる
削除されたレコードを復元することが可能
データの整合性を維持しやすい

物理的な削除処理が必要になった場合、追加の処理が必要
削除されたレコードが検索結果に表示される場合がある

子レコードの参照チェックを行う**

親レコード削除前に、関連する子レコードが存在しないことをチェックすることで、予期せぬ削除を防ぐことができます。

DELETE FROM orders
WHERE order_id = 1
AND NOT EXISTS (
  SELECT 1 FROM order_items WHERE order_id = 1
);

上記のクエリは、ordersテーブルからorder_id = 1のレコードを削除しようとした際に、order_itemsテーブルに関連するレコードが存在するかどうかをチェックします。関連レコードが存在する場合、削除処理は実行されません。

予期せぬ削除を防ぐことができる
比較的シンプルな処理

複雑な親子関係の場合、チェック処理が複雑になる可能性がある

適切な方法の選択

どの方法が適切かは、データベースの設計、データの関係性、アプリケーションの要件などを考慮して決定する必要があります。

シンプルで使いやすい方法: カスケード削除
柔軟性と制御性: トリガー
データ整合性と復元可能性: 論理削除
予期せぬ削除の防止: 参照チェック

それぞれの方法の利点と欠点を理解し、状況に応じて最適な方法を選択することが重要です。

上記以外にも、外部キー制約と参照整合性を扱うための様々なテクニックがあります。
詳細については、MySQL公式ドキュメントや書籍などを参照してください。

mysql foreign-keys innodb