Combine ON DELETE SET NULL and ON DELETE CASCADE by assigning each action to a separate foreign key. This walkthrough uses synthetic articles to show how deleting a collection can preserve its articles, while deleting an article removes its sections.

The task is to define that behavior, verify both deletion paths from a Mac’s Terminal, and repeat the checks with node-postgres. The examples use ordinary foreign keys and temporary tables, so each run has its own sample rows.

Which relationship should use each delete action?

A delete action belongs to the foreign key in the referencing table. SET NULL clears the reference when the referenced row disappears; CASCADE deletes the referencing row. PostgreSQL documents optional relationships as a suitable use for SET NULL, and dependent components as a suitable use for CASCADE. Constraints

For this example, a collection groups articles, but an article can exist without a collection. A section belongs to one article and has no independent role. These are explicit design choices for the sample:

Foreign key Chosen action Intended result
demo_articles.collection_id references demo_collections.collection_id ON DELETE SET NULL Removing a collection preserves its articles without a collection reference.
demo_sections.article_id references demo_articles.article_id ON DELETE CASCADE Removing an article removes its sections.

The resulting behavior depends on where deletion starts. Deleting a collection changes an article’s collection_id; it does not delete the article. Therefore, the section foreign key’s ON DELETE CASCADE has no article deletion to respond to. Deleting the article itself does activate that cascade. This follows from the documented definitions of the two actions. CREATE TABLE

Do not put two competing foreign keys on the same relationship to express “clear the reference and delete the row.” Decide whether that referencing row should survive, then choose the action that implements that decision.

Create a complete sample on your Mac

You need a running PostgreSQL server, an existing database you can connect to, and psql available in Terminal. The commands below use the existing local database named postgres; substitute your existing database name if necessary. Installation and server setup are outside this walkthrough.

Save the following as a plain-text file named setup.sql in a working folder. All three tables are temporary: PostgreSQL removes temporary tables when their session ends. Every referenced table in this example is also temporary, as PostgreSQL does not allow foreign keys between temporary and permanent tables. 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');

Notice that collection_id is nullable, while article_id in the sections table is required. A SET NULL action must still satisfy the table’s other constraints. The explicit indexes cover the referencing columns; PostgreSQL does not automatically create those indexes when a foreign key is declared. Constraints

Verify both deletion paths in one session

Save the next block as verify.sql beside setup.sql. It is a complete psql script: \ir includes the setup file relative to the verification script. Run both files through this script so the temporary tables remain in the same session. The -f option executes a file, and ON_ERROR_STOP stops script processing after an error. 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;

From Terminal, switch to the folder containing the files and run:

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

The first query’s expected results are derived from the sample rows:

Article Collection ID after collection deletion Collection title Section count
101 NULL NULL 2
102 2 Color Notes 1

Article 101 survives with its two sections. After the second deletion, the sections query should return only section 1003, belonging to article 102. This pair of checks distinguishes preserving an article from deleting an article and its dependent rows.

The verification query deliberately uses LEFT JOIN. PostgreSQL retains an unmatched row from the left table and supplies nulls for the missing right-hand values. An inner join to collections would omit article 101 after its reference becomes null, making a preserved article disappear from that query’s results. Table Expressions

The RETURNING clauses report rows deleted from the target tables. They do not provide a combined inventory of all sections removed by the cascade; the separate sections query verifies that outcome. DELETE

The final ROLLBACK cancels the demonstration’s changes. PostgreSQL also treats a standalone statement as a transaction, so an explicit transaction is useful here to group setup, deletion, inspection, and cleanup. Transactions

Run the same checks with node-postgres

For the JavaScript version, keep setup.sql unchanged. Use Node.js 24 for this example and install the documented pg package in the working folder: node-postgres

npm install pg

Save the following complete program as verify.mjs beside setup.sql. It reads the same synthetic fixtures, verifies that collection deletion preserves both articles and all three sections, then verifies that article deletion removes only the matching sections.

The program uses one connected client throughout the transaction, as required by node-postgres. Its query values are passed through the documented parameter-array interface using $1. Transactions, Queries

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();
}

Run it against the same existing local database:

PGDATABASE=postgres node verify.mjs

new Client() reads PostgreSQL connection environment variables. Preserve any connection settings already needed for your local server; changing the database name alone does not configure a different server. Connecting

A failed assertion enters the rollback path and surfaces an error. The success message appears only after all outcome checks pass. This program intentionally rolls back on success too, keeping it a repeatable demonstration.

The JavaScript syntax was checked during preparation. A PostgreSQL executable was unavailable in the research environment, so the database examples were inspected against the documentation rather than executed; the tables above describe expected results.

What changes with composite foreign keys?

The main example uses single-column foreign keys. A composite foreign key introduces another decision: should deletion clear every referencing column or only the optional one? PostgreSQL 15 introduced column lists for foreign-key ON DELETE SET actions; earlier versions affected every column in the foreign key. PostgreSQL 15 Release Notes

For example, a composite article reference might contain a required edition identifier and an optional related-article identifier. On PostgreSQL 15 or later, ON DELETE SET NULL (related_article_id) can preserve the edition identifier while clearing the optional article reference. Without that column list, SET NULL targets both columns. This syntax is a PostgreSQL extension and is available for ON DELETE, not ON UPDATE. CREATE TABLE

Partial nulling also requires a compatible match rule. The default MATCH SIMPLE permits a composite foreign key containing a null; MATCH FULL rejects a mixture of null and non-null referencing values. Therefore, retaining a required edition identifier while nulling only the related-article identifier is incompatible with MATCH FULL. Constraints

On PostgreSQL 14, use a schema with a separate single-column optional reference, as in the main example, when that accurately represents the relationship. If the composite relationship must remain, explicitly clear its optional reference before deleting the referenced row, using one transaction. That is an application-managed sequence rather than the unsupported column-list syntax.

There is another documented boundary in PostgreSQL 18: temporal foreign keys using PERIOD do not support CASCADE or SET NULL. The ordinary foreign keys demonstrated here are the supported path for these delete actions when duration coverage is not part of the relationship. CREATE TABLE

How do you diagnose an unexpected result?

Use the deletion path as your diagnostic sequence:

  1. Confirm the referencing table. Inspect \d demo_articles and \d demo_sections in the session containing the tables. Check the actual foreign-key actions rather than inferring them from column names. PostgreSQL documents \d as a way to inspect table details and constraint names. Modifying Tables
  2. Check nullability and other constraints. If collection deletion fails, confirm that the column being cleared permits null and that other constraints accept the resulting row. SET NULL does not bypass them. Constraints
  3. Inspect every relevant foreign key. An additional relationship may have a different action. An omitted action defaults to NO ACTION, which can prevent deletion when references remain. CREATE TABLE
  4. Query the base table before interpreting a join. If the article still exists there, inspect joins and filters that may omit its new null reference. Table Expressions
  5. Keep transaction statements on one client. In a pooled application, acquire one client for the whole transaction. node-postgres explicitly advises against using pool.query() for transaction statements. Transactions

For this article model, the acceptance test is concrete: removing collection 1 leaves article 101 and its sections intact, while removing article 101 leaves only article 102’s section. Keep those two checks together when adapting the schema so the intended survival and deletion behavior remains visible.

Sources