PostgreSQLでON DELETE SET NULLとCASCADEを組み合わせる

ON DELETE SET NULLとON DELETE CASCADEは、それぞれのアクションを別々の外部キーに割り当てることで組み合わせられます。この手順では、架空の記事を使って、コレクションを削除しても記事を残し、記事を削除するとセクションを削除する方法を示します。

ここでは、その動作を定義し、MacのTerminalから両方の削除経路を検証してから、node-postgresでも同じ確認を行います。例では通常の外部キーと一時テーブルを使うため、実行のたびにサンプル行が用意されます。

それぞれの削除アクションはどのリレーションに使うべきですか？

削除アクションは、参照するテーブルの外部キーに設定します。参照先の行がなくなったとき、SET NULLは参照をクリアし、CASCADEは参照する行を削除します。PostgreSQLのドキュメントでは、任意のリレーションにはSET NULL、依存する構成要素にはCASCADEが適しているとされています。制約

この例では、コレクションは記事をまとめますが、記事はコレクションなしでも存在できます。セクションは1つの記事に属し、単独では役割を持ちません。これらは、このサンプルで明示的に選んだ設計です。

外部キー	選択したアクション	意図する結果
demo_articles.collection_idはdemo_collections.collection_idを参照	ON DELETE SET NULL	コレクションを削除しても記事は残り、コレクションへの参照はなくなります。
demo_sections.article_idはdemo_articles.article_idを参照	ON DELETE CASCADE	記事を削除すると、そのセクションも削除されます。

結果は、どこから削除を開始するかによって変わります。コレクションを削除すると、記事のcollection_idが変更されますが、記事自体は削除されません。そのため、セクションの外部キーに設定されたON DELETE CASCADEは、記事の削除に反応しません。記事自体を削除すると、このカスケードが実行されます。これは、2つのアクションについてドキュメントに記載された定義に従っています。CREATE TABLE

「参照をクリアして行を削除する」という動作を表すために、同じリレーションに競合する外部キーを2つ設定しないでください。参照する行を残すかどうかを決め、その判断に合ったアクションを選びます。

Macでサンプル一式を作成する

実行中のPostgreSQLサーバー、接続可能な既存のデータベース、およびTerminalで使えるpsqlが必要です。以下のコマンドでは、既存のローカルデータベースpostgresを使います。必要に応じて、使用中の既存データベース名に置き換えてください。インストールとサーバーのセットアップは、この手順の対象外です。

次の内容を、作業フォルダーにsetup.sqlという名前のプレーンテキストファイルとして保存します。3つのテーブルはすべて一時テーブルです。PostgreSQLはセッション終了時に一時テーブルを削除します。この例で参照するテーブルもすべて一時テーブルです。PostgreSQLでは、一時テーブルと永続テーブルの間に外部キーを設定できないためです。CREATE TABLE

CREATE TEMP TABLE demo_collections (
    collection_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TEMP TABLE demo_articles (
    article_id integer PRIMARY KEY,
    collection_id integer,
    title text NOT NULL,
    CONSTRAINT demo_articles_collection_fk
        FOREIGN KEY (collection_id)
        REFERENCES demo_collections (collection_id)
        ON DELETE SET NULL
);

CREATE TEMP TABLE demo_sections (
    section_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    heading text NOT NULL,
    CONSTRAINT demo_sections_article_fk
        FOREIGN KEY (article_id)
        REFERENCES demo_articles (article_id)
        ON DELETE CASCADE
);

CREATE INDEX demo_articles_collection_idx
    ON demo_articles (collection_id);
CREATE INDEX demo_sections_article_idx
    ON demo_sections (article_id);

INSERT INTO demo_collections (collection_id, title) VALUES
    (1, 'Layout Notes'),
    (2, 'Color Notes');

INSERT INTO demo_articles (article_id, collection_id, title) VALUES
    (101, 1, 'Grid Spacing'),
    (102, 2, 'Color Pairings');

INSERT INTO demo_sections (section_id, article_id, heading) VALUES
    (1001, 101, 'Overview'),
    (1002, 101, 'Examples'),
    (1003, 102, 'Overview');

collection_idはNULLを許容しますが、sectionsテーブルのarticle_idは必須であることに注意してください。SET NULLアクションを使う場合も、テーブルのほかの制約を満たす必要があります。明示的に作成したインデックスは参照列を対象としています。外部キーを宣言しても、PostgreSQLがこれらのインデックスを自動作成することはありません。制約

1つのセッションで両方の削除経路を検証する

次のブロックをsetup.sqlと同じ場所にverify.sqlとして保存します。これは完全なpsqlスクリプトです。\irで、検証スクリプトからの相対パスでセットアップファイルを読み込みます。一時テーブルを同じセッション内に保つため、両方のファイルをこのスクリプト経由で実行してください。-fオプションはファイルを実行し、ON_ERROR_STOPはエラーが発生するとスクリプトの処理を停止します。psql

BEGIN;
\ir setup.sql

DELETE FROM demo_collections
WHERE collection_id = 1
RETURNING collection_id;

SELECT a.article_id,
       a.title,
       a.collection_id,
       c.title AS collection_title,
       count(s.section_id) AS section_count
FROM demo_articles AS a
LEFT JOIN demo_collections AS c
    ON c.collection_id = a.collection_id
LEFT JOIN demo_sections AS s
    ON s.article_id = a.article_id
GROUP BY a.article_id, a.title, a.collection_id, c.title
ORDER BY a.article_id;

DELETE FROM demo_articles
WHERE article_id = 101
RETURNING article_id;

SELECT section_id, article_id, heading
FROM demo_sections
ORDER BY section_id;

ROLLBACK;

Terminalでファイルのあるフォルダーに移動し、次を実行します。

psql -X -v ON_ERROR_STOP=1 -d postgres -f verify.sql

最初のクエリの期待結果は、サンプル行から次のように導けます。

記事	コレクション削除後のコレクションID	コレクション名	セクション数
101	NULL	NULL	2
102	2	Color Notes	1

記事101は、2つのセクションとともに残ります。2回目の削除後、セクションのクエリには、記事102に属するセクション1003だけが返るはずです。この2つの確認により、記事を残す場合と、記事および依存する行を削除する場合を区別できます。

検証クエリでは意図的にLEFT JOINを使っています。PostgreSQLは左側のテーブルに一致する行がない場合もその行を保持し、右側の値をNULLにします。コレクションへの内部結合を使うと、参照がNULLになった後の記事101が結果から除外され、残っている記事がクエリ上で見えなくなります。テーブル式

RETURNING句は、対象テーブルから削除された行を報告します。カスケードによって削除されたセクションすべての一覧をまとめて返すものではありません。その結果は、別のセクションクエリで検証します。DELETE

最後のROLLBACKは、デモで行った変更を取り消します。PostgreSQLでは単独のステートメントもトランザクションとして扱われるため、セットアップ、削除、確認、後片付けをまとめるには明示的なトランザクションが便利です。トランザクション

node-postgresで同じ確認を行う

JavaScript版では、setup.sqlを変更せずに使います。この例ではNode.js 24を使い、作業フォルダーにドキュメント記載のpgパッケージをインストールします。node-postgres

npm install pg

次の完全なプログラムを、setup.sqlと同じ場所にverify.mjsとして保存します。このプログラムは同じ架空のテストデータを読み込み、コレクションを削除しても両方の記事と3つすべてのセクションが残ることを検証します。続けて、記事を削除すると該当するセクションだけが削除されることを検証します。

プログラムはトランザクション全体を通して1つの接続クライアントを使います。これはnode-postgresで必要な方法です。クエリの値は、ドキュメントに記載されたパラメーター配列のインターフェースを使い、$1経由で渡します。トランザクション、クエリ

import pg from 'pg';
import { readFile } from 'node:fs/promises';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client();
const setup = await readFile(
  new URL('./setup.sql', import.meta.url),
  'utf8'
);

await client.connect();
try {
  await client.query('BEGIN');
  await client.query(setup);

  const removed = await client.query(
    'DELETE FROM demo_collections WHERE collection_id = $1 RETURNING collection_id',
    [1]
  );
  assert.deepEqual(removed.rows, [{ collection_id: 1 }]);

  const articles = await client.query(
    'SELECT article_id, collection_id FROM demo_articles ORDER BY article_id'
  );
  assert.deepEqual(articles.rows, [
    { article_id: 101, collection_id: null },
    { article_id: 102, collection_id: 2 }
  ]);

  const before = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(
    before.rows.map(row => row.section_id),
    [1001, 1002, 1003]
  );

  await client.query(
    'DELETE FROM demo_articles WHERE article_id = $1',
    [101]
  );
  const after = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(after.rows, [{ section_id: 1003 }]);

  console.log('Expected SET NULL and CASCADE outcomes verified.');
  await client.query('ROLLBACK');
} catch (error) {
  try {
    await client.query('ROLLBACK');
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError.message);
  }
  throw error;
} finally {
  await client.end();
}

同じ既存のローカルデータベースに接続して実行します。

PGDATABASE=postgres node verify.mjs

new Client()はPostgreSQLの接続環境変数を読み取ります。ローカルサーバーに必要な接続設定がすでにあれば、そのまま使用してください。データベース名だけを変更しても、別のサーバーへの接続設定にはなりません。接続

アサーションに失敗すると、ロールバック処理に入り、エラーが返されます。成功メッセージは、すべての結果確認に合格した後にのみ表示されます。このプログラムは、成功時も意図的にロールバックし、繰り返し実行できるデモとして使えるようにしています。

JavaScriptの構文は準備中に確認済みです。調査環境にはPostgreSQLの実行ファイルがなかったため、データベースの例は実行せず、ドキュメントと照合して確認しました。上の表には期待される結果を示しています。

複合外部キーでは何が変わりますか？

主な例では、単一列の外部キーを使っています。複合外部キーでは、参照するすべての列をクリアするか、任意の列だけをクリアするかを決める必要があります。PostgreSQL 15では、外部キーのON DELETE SETアクションに列リストが導入されました。それ以前のバージョンでは、外部キーのすべての列が対象でした。PostgreSQL 15リリースノート

たとえば、複合記事参照に、必須の版識別子と任意の関連記事識別子が含まれるとします。PostgreSQL 15以降では、ON DELETE SET NULL (related_article_id)を使い、版識別子を残したまま任意の記事参照をクリアできます。列リストを指定しない場合、SET NULLは両方の列を対象にします。この構文はPostgreSQLの拡張機能で、ON UPDATEではなくON DELETEで使えます。CREATE TABLE

一部の列だけをNULLにする場合は、互換性のある照合ルールも必要です。デフォルトのMATCH SIMPLEでは、NULLを含む複合外部キーが許可されます。MATCH FULLでは、参照値にNULLと非NULLが混在することは許可されません。そのため、必須の版識別子を残して関連記事識別子だけをNULLにする方法は、MATCH FULLとは併用できません。制約

PostgreSQL 14では、主な例のように、リレーションを正しく表せる場合は任意の単一列参照を別に設けるスキーマを使います。複合リレーションを維持する必要がある場合は、トランザクション内で参照先の行を削除する前に、任意の参照を明示的にクリアします。これはアプリケーションで管理する手順であり、未対応の列リスト構文ではありません。

PostgreSQL 18には、ドキュメントに記載された別の制限があります。PERIODを使う期間外部キーでは、CASCADEもSET NULLもサポートされません。リレーションに期間の網羅性が含まれない場合、この例のような通常の外部キーが、これらの削除アクションを使うためのサポート対象の方法です。CREATE TABLE

想定外の結果はどう診断しますか？

診断には、削除の経路に沿って次の手順を使います。

1. 参照するテーブルを確認する。 テーブルが存在するセッションで\d demo_articlesと\d demo_sectionsを調べます。列名から推測せず、実際の外部キーアクションを確認してください。PostgreSQLのドキュメントでは、テーブルの詳細や制約名を調べる方法として\dが紹介されています。テーブルの変更
2. NULL許容とほかの制約を確認する。 コレクションの削除に失敗する場合は、クリアされる列がNULLを許容することと、変更後の行がほかの制約を満たすことを確認します。SET NULLは制約を無視しません。制約
3. 関連する外部キーをすべて確認する。 別のリレーションに異なるアクションが設定されている場合があります。アクションを省略するとNO ACTIONがデフォルトになり、参照が残っている場合は削除を妨げることがあります。CREATE TABLE
4. 結合を解釈する前に、ベーステーブルを検索する。 記事がベーステーブルに残っている場合は、参照がNULLになったために記事を除外している結合やフィルターがないか調べます。テーブル式
5. 同じクライアントでトランザクションのステートメントを実行する。 接続プールを使うアプリケーションでは、トランザクション全体で1つのクライアントを取得します。node-postgresは、トランザクションのステートメントにpool.query()を使わないよう明示しています。トランザクション

この記事モデルでは、受け入れテストを具体的に定義できます。コレクション1を削除しても記事101とそのセクションが残り、記事101を削除すると、記事102のセクションだけが残ります。スキーマを変更する場合も、この2つの確認を組み合わせて、意図した保持と削除の動作を確認できるようにしてください。

出典

• 制約 — PostgreSQL Global Development Group
• CREATE TABLE — PostgreSQL Global Development Group
• psql — PostgreSQL Global Development Group
• テーブル式 — PostgreSQL Global Development Group
• DELETE — PostgreSQL Global Development Group
• トランザクション — PostgreSQL Global Development Group
• PostgreSQL 15リリースノート — PostgreSQL Global Development Group
• テーブルの変更 — PostgreSQL Global Development Group
• node-postgres — node-postgres
• トランザクション — node-postgres
• クエリ — node-postgres
• 接続 — node-postgres