PostgreSQLでCASCADE DELETE just onceを安全に利用するためのヒント
PostgreSQLにおける「CASCADE DELETE just once」
PostgreSQLでは、CASCADE DELETE オプションを使用することで、親テーブルのレコードを削除するときに、関連する子テーブルのレコードも自動的に削除することができます。しかし、場合によっては、最初のレベルの子テーブルのみを削除し、さらに深いレベルの子テーブルには影響を与えたくない場合があります。
「CASCADE DELETE just once」とは
「CASCADE DELETE just once」は、最初のレベルの子テーブルのみを削除し、それ以降の子テーブルには影響を与えない方法です。これは、DELETE ステートメントに LIMIT 1 を追加することで実現できます。
例
以下の例では、products テーブルと orders テーブルの関係は、product_id 列で 1 対多 となっています。
-- テーブル定義
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER
);
-- データ挿入
INSERT INTO products (name) VALUES ('商品1'), ('商品2');
INSERT INTO orders (product_id, quantity) VALUES (1, 10), (1, 20), (2, 30);
通常の CASCADE DELETE
DELETE FROM products
WHERE product_id = 1;
このクエリを実行すると、products テーブルから product_id=1 のレコードが削除されます。同時に、orders テーブルから product_id=1 のレコードも すべて 削除されます。
DELETE FROM products
WHERE product_id = 1
LIMIT 1;
注意事項
LIMIT 1を使用すると、最初のレベルの子テーブルのみが削除されます。- 複数のレベルの子テーブルを削除したい場合は、サブクエリを使用する必要があります。
LIMITを使用すると、パフォーマンスが低下する可能性があります。
環境
- PostgreSQL 14.2
- psql コマンドラインツール
テーブル定義
-- テーブル定義
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER
);
-- データ挿入
INSERT INTO products (name) VALUES ('商品1'), ('商品2'), ('商品3');
INSERT INTO orders (product_id, quantity) VALUES (1, 10), (1, 20), (2, 30), (3, 40);
-- 親テーブルのレコードを削除
DELETE FROM products
WHERE product_id = 1;
-- 結果確認
SELECT * FROM products;
-- product_id | name
-- ----------+---------
-- 2 | 商品2
-- 3 | 商品3
SELECT * FROM orders;
-- order_id | product_id | quantity
-- ---------+-----------+---------
-- 3 | 3 | 40
-- 親テーブルのレコードを削除 (LIMIT 1 使用)
DELETE FROM products
WHERE product_id = 1
LIMIT 1;
-- 結果確認
SELECT * FROM products;
-- product_id | name
-- ----------+---------
-- 2 | 商品2
-- 3 | 商品3
SELECT * FROM orders;
-- order_id | product_id | quantity
-- ---------+-----------+---------
-- 1 | 1 | 10
-- 2 | 1 | 20
-- 3 | 3 | 40
サブクエリを使用した多階層の CASCADE DELETE
-- 親テーブルのレコードを削除
DELETE FROM products
WHERE product_id IN (
SELECT product_id
FROM orders
WHERE quantity > 10
);
-- 結果確認
SELECT * FROM products;
-- product_id | name
-- ----------+---------
-- 3 | 商品3
SELECT * FROM orders;
-- order_id | product_id | quantity
-- ---------+-----------+---------
-- 3 | 3 | 40
上記のクエリを実行すると、orders テーブルで quantity が 10 を超えるレコードの product_id を取得し、その product_id を持つ products テーブルのレコードを削除します。orders テーブルの quantity が 10 以下のレコードは影響を受け ません。
LIMIT によるパフォーマンスへの影響
LIMIT を使用すると、パフォーマンスが低下する可能性があります。大量のデータを削除する場合は、サブクエリを使用するなど、他の方法を検討する必要があります。
CASCADE DELETE just once の代替方法
サブクエリを使用することで、削除したい子テーブルのレコードを特定することができます。
DELETE FROM products
WHERE product_id IN (
SELECT product_id
FROM orders
WHERE product_id = 1
);
上記のクエリは、orders テーブルで product_id=1 のレコードの product_id を取得し、その product_id を持つ products テーブルのレコードを削除します。
UPDATE と JOIN を使用することで、子テーブルのレコードを削除フラグで更新し、その後削除フラグが立っているレコードのみを削除することができます。
UPDATE orders
SET is_deleted = TRUE
WHERE product_id = 1;
DELETE FROM orders
WHERE is_deleted = TRUE;
上記のクエリは、orders テーブルで product_id=1 のレコードの is_deleted 列を TRUE に更新し、その後 is_deleted 列が TRUE のレコードのみを削除します。
トリガーを使用することで、親テーブルのレコードが削除されたときに、自動的に子テーブルのレコードを削除することができます。
CREATE TRIGGER delete_orders
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
DELETE FROM orders
WHERE product_id = OLD.product_id;
END;
上記のトリガーは、products テーブルのレコードが削除される前に、orders テーブルから product_id が一致するレコードを削除します。
- データ量が少ない場合は、
LIMIT 1を使用するのが最も簡単です。 - データ量が多い場合は、サブクエリや UPDATE と JOIN を使用した方がパフォーマンスが良い場合があります。
- 複雑なロジックが必要な場合は、トリガーを使用するのが良い場合があります。
postgresql sql-delete cascade
