SQL ON DELETE CASCADE とは？データベースの整合性を保つための重要な機能

2024-04-11

SQL ON DELETE CASCADE とは？

例:

親テーブル: 顧客

顧客レコードが削除された場合、その顧客に関連する注文レコードもすべて削除する必要があります。ON DELETE CASCADE を使用すると、この処理を自動的に実行できます。

ON DELETE CASCADE の利点:

データベースの整合性を保つことができる
コードを簡潔に記述できる
データベース操作を効率化できる

子テーブルのレコードが意図せず削除される可能性がある
複雑なデータ構造の場合、予期せぬ結果になる可能性がある

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- 顧客レコードを削除
DELETE FROM customers WHERE id = 1;

-- 関連する注文レコードも自動的に削除される
SELECT * FROM orders WHERE customer_id = 1;

ON DELETE SET NULL: 子テーブルのレコードの外部キー列を NULL に設定します。
ON DELETE RESTRICT: 親テーブルのレコードを削除できないようにします。

SQL ON DELETE CASCADE は、データベースの整合性を保つために役立つ機能です。使用する際には、利点と注意点をよく理解した上で、適切なオプションを選択するようにしましょう。

テーブルの作成

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

データの挿入

INSERT INTO customers (name) VALUES ('John Doe');
INSERT INTO orders (customer_id) VALUES (1);

INSERT INTO customers (name) VALUES ('Jane Doe');
INSERT INTO orders (customer_id) VALUES (2);

顧客レコードの削除

DELETE FROM customers WHERE id = 1;

結果

SELECT * FROM customers;

id | name
------- | --------
2 | Jane Doe

SELECT * FROM orders;

id | customer_id
------- | --------
2 | 2

解説

顧客テーブル customers と注文テーブル orders を作成します。
orders テーブルの customer_id 列は、customers テーブルの id 列を参照する外部キーです。
ON DELETE CASCADE オプションを指定しているので、customers テーブルのレコードが削除されると、関連する orders テーブルのレコードも自動的に削除されます。
customers テーブルの ID が 1 のレコードを削除します。
すると、orders テーブルで customer_id が 1 のレコードも自動的に削除されます。

ON DELETE CASCADE は、親子関係にあるテーブル間で使用するものです。
複雑なデータ構造の場合、ON DELETE CASCADE の使用には注意が必要です。
データの整合性を保つために、ON DELETE CASCADE と他のオプションを組み合わせて使用することもできます。

SQL ON DELETE CASCADE の代替方法

代替方法:

トリガー: 削除処理をカスタマイズするためにトリガーを作成します。

それぞれの方法の利点と欠点:

ON DELETE SET NULL:

利点:

子テーブルのレコードを保持できる

子テーブルのレコードを誤って削除することを防ぐことができる

親テーブルのレコードを削除できない場合がある

トリガー:

削除処理をカスタマイズできる

複雑な処理になる場合がある

以下は、それぞれの方法の例です。

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

-- 顧客レコードを削除
DELETE FROM customers WHERE id = 1;

-- 関連する注文レコードの customer_id 列は NULL になる
SELECT * FROM orders WHERE customer_id = 1;

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

-- 顧客レコードを削除しようとするとエラーが発生
DELETE FROM customers WHERE id = 1;

ERROR: Cannot delete or update a parent row: a foreign key constraint fails (`orders`.`customer_id`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

CREATE TRIGGER delete_customer_trigger
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.id;
END;

-- 顧客レコードを削除
DELETE FROM customers WHERE id = 1;

-- 関連する注文レコードも削除される
SELECT * FROM orders WHERE customer_id = 1;