PostgreSQLで外部キー制約を持つ行の削除を厳格に制御！ON DELETE RESTRICTとON DELETE SET NULL

2024-04-24

PostgreSQLで外部キーを持つ行を削除する方法

カスケード削除を使用する

最も簡単な方法は、CASCADE オプションを使用して外部キー制約を作成することです。これにより、親テーブルの行が削除されると、参照している子テーブルの行も自動的に削除されます。

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR(255)
);

上記の例では、orders テーブルの customer_id 列は、customers テーブルの customer_id 列を参照する外部キー制約です。 CASCADE オプションが指定されているため、customers テーブルから顧客レコードを削除すると、それに関連するすべての注文レコードも自動的に削除されます。

利点:

シンプルで使いやすい
参照整合性を自動的に維持する

予期せぬ削除が発生する可能性があるため、注意が必要です。

DELETE 構文を使用して、外部キーを持つ行を明示的に削除することもできます。ただし、この方法を使用する場合は、参照整合性エラーが発生しないように、子テーブルから親テーブルへの参照を先に削除する必要があります。

DELETE FROM orders
WHERE customer_id = 123;

DELETE FROM customers
WHERE customer_id = 123;

上記の例では、orders テーブルから顧客 ID 123 に関連するすべての注文レコードが最初に削除されます。その後、customers テーブルから顧客 ID 123 のレコードが削除されます。この順序で削除しないと、customers テーブルを削除しようとしたときに参照整合性エラーが発生します。

削除する行をより細かく制御できます。

CASCADE を使用する場合よりも複雑
参照整合性エラーを手動で処理する必要がある

ON DELETE RESTRICT または ON DELETE SET NULL を使用する

CASCADE 以外のオプションとして、ON DELETE RESTRICT または ON DELETE SET NULL を使用して外部キー制約を作成することもできます。

ON DELETE RESTRICT：親テーブルの行が削除されると、参照している子テーブルの行の削除を禁止します。

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT
);

参照整合性をより厳密に制御できます。

アプリケーションロジックでエラー処理を行う必要がある

適切な方法を選択する

シンプルで使いやすい方法は、CASCADE を使用する方法です。
削除する行をより細かく制御したい場合は、DELETE 構文を使用する方法が適しています。
参照整合性をより厳密に制御したい場合は、ON DELETE RESTRICTまたはON DELETE SET NULL` を使用する 方法が適しています。

その他の注意事項

外部キー制約を削除する前に、その制約に依存しているデータがないことを確認してください。
TRUNCATE 構文を使用すると、テーブル内のすべての行が削除されますが、外部キー制約は考慮されません。

PostgreSQLにおける外部キー制約のサンプルコード

カスケード削除を使用する

-- customersテーブルを作成
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR(255)
);

-- ordersテーブルを作成。customer_id列は、customersテーブルのcustomer_id列を参照する外部キー制約を持つ
CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id)
);

-- 顧客を追加
INSERT INTO customers (name) VALUES ('Taro Yamada');
INSERT INTO customers (name) VALUES ('Hanako Sato');

-- 顧客Taro Yamadaの注文を追加
INSERT INTO orders (customer_id) VALUES (1);

-- 顧客Taro Yamadaを削除すると、関連する注文も自動的に削除される
DELETE FROM customers WHERE customer_id = 1;

-- 顧客Taro Yamadaが削除されたことを確認
SELECT * FROM customers;
-- 結果: 空のテーブル

-- 顧客Hanako Satoの注文を確認
SELECT * FROM orders;
-- 結果: order_id 1 のレコードのみ

DELETE 構文を使用する

-- customersテーブルを作成
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR(255)
);

-- ordersテーブルを作成。customer_id列は、customersテーブルのcustomer_id列を参照する外部キー制約を持つ
CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id)
);

-- 顧客を追加
INSERT INTO customers (name) VALUES ('Taro Yamada');
INSERT INTO customers (name) VALUES ('Hanako Sato');

-- 顧客Taro Yamadaの注文を追加
INSERT INTO orders (customer_id) VALUES (1);

-- 子テーブル(orders)から親テーブル(customers)への参照を削除
DELETE FROM orders WHERE customer_id = 1;

-- 親テーブル(customers)から顧客Taro Yamadaを削除
DELETE FROM customers WHERE customer_id = 1;

-- 顧客Taro Yamadaが削除されたことを確認
SELECT * FROM customers;
-- 結果: 空のテーブル

-- 顧客Hanako Satoの注文を確認
SELECT * FROM orders;
-- 結果: order_id 1 のレコードのみ

ON DELETE RESTRICT または ON DELETE SET NULL を使用する

ON DELETE RESTRICT

-- customersテーブルを作成
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR(255)
);

-- ordersテーブルを作成。customer_id列は、customersテーブルのcustomer_id列を参照する外部キー制約を持つ
CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT
);

-- 顧客を追加
INSERT INTO customers (name) VALUES ('Taro Yamada');
INSERT INTO customers (name) VALUES ('Hanako Sato');

-- 顧客Taro Yamadaの注文を追加
INSERT INTO orders (customer_id) VALUES (1);

-- 顧客Taro Yamadaを削除しようとすると、参照エラーが発生
DELETE FROM customers WHERE customer_id = 1;
-- エラー: 違反: DELETE FROM "customers" WHERE "customer_id" = 1 は、"orders" の "customer_id" 列に参照を持つため実行できません

-- 顧客Hanako Satoの注文を確認
SELECT * FROM orders;
-- 結果: order_id 1 のレコードのみ

-- customersテーブルを作成
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR(255)
);

-- ordersテーブルを作成。customer_id列は、customersテーブルのcustomer_id列を参照する外部キー制約を持つ
CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(customer_id) ON DELETE SET NULL
);

-- 顧客を追加
INSERT INTO customers (name) VALUES ('Taro Yamada');
INSERT INTO customers (name) VALUES ('Hanako Sato');

-- 顧客Taro Yamadaの注文を追加
INSERT INTO orders (customer_id) VALUES (1);

-- 顧客Taro Yamadaを削除
DELETE FROM customers WHERE customer_id = 1;

-- 顧客Taro Yamadaが削除されたことを確認
SELECT * FROM customers;
-- 結果: 空のテーブル

-- 顧客Hanako Satoの注文を確認
SELECT *

PostgreSQLで外部キーを持つ行を削除するその他の方法

トリガーを使用して、親テーブルの行が削除されたときに子テーブルの対応する行を自動的に削除できます。

CREATE OR REPLACE FUNCTION delete_order_on_customer_delete()
RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM orders WHERE customer_id = OLD.customer_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_order_before_customer_delete
BEFORE DELETE ON customers
FOR EACH ROW
EXECUTE PROCEDURE delete_order_on_customer_delete();

上記の例では、delete_order_on_customer_delete というトリガーが作成されます。このトリガーは、customers テーブルから行が削除される前に実行されます。トリガーは、DELETE 構文を使用して、削除される顧客に関連するすべての注文を削除します。

複雑な削除ロジックをカプセル化できる

トリガーのデバッグとトラブルシューティングが複雑になる可能性がある

非同期処理を使用する

ジョブキューなどの非同期処理システムを使用して、親テーブルの行が削除された後に子テーブルの対応する行を削除することもできます。

import psycopg2

def delete_orders_for_customer(customer_id):
  # PostgreSQLに接続
  conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")
  cur = conn.cursor()

  # 削除する注文をすべて取得
  cur.execute("SELECT order_id FROM orders WHERE customer_id = %s", (customer_id,))
  order_ids = cur.fetchall()

  # 各注文を削除
  for order_id in order_ids:
    cur.execute("DELETE FROM orders WHERE order_id = %s", (order_id,))

  # コミットして変更を保存
  conn.commit()
  conn.close()

# 顧客123を削除
delete_customer(123)

# 非同期ジョブとしてdelete_orders_for_customerを実行
# ...