How PostgreSQL 17 Deferred Constraints Simplify Many-to-Many Deletes Without Cascades

Foreign keys protect data integrity, but sometimes they make operations feel unnatural. A common example is deleting a parent row while junction rows in a many-to-many relationship still reference it.

By default, relational databases require you to delete the children first. Many developers turn to ON DELETE CASCADE, but in some businesses — for audit or compliance reasons — cascades are forbidden.

This post shows how PostgreSQL 17’s deferred constraints and MERGE RETURNING let you design a natural, parent-first workflow using a real XML import example, and contrasts it with SQL Server.

This post explains:

• What deferred constraint checks are in PostgreSQL
• How deferrable foreign keys work (and when to use them)
• A clean CTE + transaction pattern to delete a parent before cleaning up a many-to-many table
• Practical alternatives in SQL Server, which does not support deferred constraints
  

---

1. What Is a Deferred Constraint Check?

By default, PostgreSQL checks foreign keys immediately: if you delete a parent row that still has referencing children, the statement fails.

If you define a foreign key as DEFERRABLE, PostgreSQL can delay validation until the end of the transaction. That allows you to:

• Perform intermediate steps that would otherwise violate the constraint
• Fix those violations later in the same transaction
• Commit only if all constraints are satisfied by the end
  

Key Terms

• DEFERRABLE: The constraint can be deferred.
• NOT DEFERRABLE: The default; checks are always immediate.
• INITIALLY DEFERRED: Constraint starts deferred for the transaction.
• INITIALLY IMMEDIATE: Constraint starts immediate but can be switched to deferred with SET CONSTRAINTS.
  

Example:

CONSTRAINT fk_example
  FOREIGN KEY (col)
  REFERENCES parent_table(col)
  DEFERRABLE INITIALLY DEFERRED;

This means:

• The foreign key exists and is enforced.
• It’s only checked at commit time, not per statement.
• Intermediate operations can temporarily look invalid, as long as the final state is valid.
  

Sample Schema

INSERT INTO author (name) VALUES ('Alice'), ('Bob');
INSERT INTO book (title) VALUES ('Postgres 101'), ('Advanced SQL');

-- Suppose Alice = id 1, Bob = id 2; Postgres 101 = id 1, Advanced SQL = id 2
INSERT INTO author_book (author_id, book_id, role) VALUES
(1, 1, 'Author'),
(2, 1, 'Co-Author'),
(2, 2, 'Author');

Example A: Default (NOT DEFERRABLE, INITIALLY IMMEDIATE)

ALTER TABLE public.author_book 
  ADD CONSTRAINT fk_author_book_author
  FOREIGN KEY (author_id) REFERENCES public.author(author_id) ON DELETE RESTRICT;

ALTER TABLE public.author_book 
  ADD CONSTRAINT fk_author_book_book
  FOREIGN KEY (book_id) REFERENCES public.book(book_id) ON DELETE RESTRICT;

DO $$
BEGIN
  DELETE FROM author a WHERE a.name = 'Alice';
  DELETE FROM author_book WHERE author_id = 1;
END
$$;

This fails with:

ERROR: update or delete on table "author" violates foreign key constraint "fk_author_book_author" on table "author_book"

Because the FK is checked immediately, the delete fails.

However, you can solve this with a CTE:

WITH deleted_author AS (
  DELETE FROM author WHERE name = 'Alice' RETURNING author_id
)
DELETE FROM author_book ab
USING deleted_author da
WHERE da.author_id = ab.author_id;

PostgreSQL treats the CTE as a single statement, so the delete succeeds.

Example B: DEFERRABLE + SET CONSTRAINTS DEFERRED

ALTER TABLE public.author_book 
  ADD CONSTRAINT fk_author_book_author
  FOREIGN KEY (author_id) REFERENCES public.author(author_id) DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE public.author_book 
  ADD CONSTRAINT fk_author_book_book
  FOREIGN KEY (book_id) REFERENCES public.book(book_id) DEFERRABLE INITIALLY IMMEDIATE;

DO $$
BEGIN
  -- Defer the FK check
  SET CONSTRAINTS fk_author_book_author, fk_author_book_book DEFERRED;

  DELETE FROM author a WHERE a.name = 'Alice';
  DELETE FROM author_book WHERE author_id = 1;
END
$$;

Here, the violation is allowed during the transaction. By commit time, the child row is gone, so the constraint passes.

---

2. The Challenge: XML Import With Many-to-Many

Suppose we import authors and their books from an XML feed:

• A book can have one or multiple authors.
• Authors are identified by name.
• Books are identified by title (business rule).
• On each XML populate data for only on author and when it update, the author’s book list can add or remove entries.
• Business rules forbid ON DELETE CASCADE.
  

The goal:

• Keep books up to date (insert/update/delete titles).
• Synchronize author_book: insert new links, delete missing ones.
• Do this in a natural, auditable way.
  

---

3. PostgreSQL 17 Solution: MERGE + RETURNING + merge_action()

PostgreSQL 17 introduced MERGE ... RETURNING and merge_action(). Together, they make this workflow elegant.

DO $$
DECLARE
  l_author_id int8;
BEGIN
  CREATE TEMPORARY TABLE xml_loading (
     author_name text NOT NULL,
     book_title  text NOT NULL
  ) ON COMMIT DROP;

  INSERT INTO xml_loading VALUES ('Alan', 'Angular 101');
  INSERT INTO xml_loading VALUES ('Alan', 'Angular Best Practice');

  -- Merge authors
  WITH merged_author AS (
    MERGE INTO author AS tgt
    USING (SELECT DISTINCT author_name FROM xml_loading) AS src
      ON src.author_name = tgt.name
    WHEN MATCHED THEN
      UPDATE SET updated_ts = NOW()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (name) VALUES (src.author_name)
    RETURNING tgt.author_id
  )
  SELECT DISTINCT author_id INTO l_author_id FROM merged_author;

  -- Merge books
  WITH merged_books AS (
    MERGE INTO book AS tgt
    USING xml_loading AS src
      ON tgt.title = src.book_title
    WHEN MATCHED THEN
      UPDATE SET updated_ts = NOW()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (title) VALUES (src.book_title)
    WHEN NOT MATCHED BY SOURCE
      AND book_id IN (
        SELECT book_id FROM author_book WHERE author_id = l_author_id
      ) THEN DELETE
    RETURNING tgt.book_id, tgt.title, merge_action() AS book_action
  ),
  desired_links AS (
    SELECT l_author_id AS author_id, mb.book_id
    FROM merged_books mb
    WHERE mb.book_action != 'DELETE'
  )
  MERGE INTO author_book AS tgt
  USING desired_links AS src
    ON (tgt.author_id = src.author_id AND tgt.book_id = src.book_id)
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (author_id, book_id) VALUES (src.author_id, src.book_id)
  WHEN NOT MATCHED BY SOURCE AND tgt.author_id = author_id THEN DELETE;
END
$$;

Why This Works

• Authors stay global (no deletes, only inserts/updates).
• Books are synchronized for the current author.
• Junction links are synced: new ones added, old ones removed.
• Both MERGEs can log their work via merge_action().
• FKs are valid by the end of the statement, so the operation is atomic.
  

---

4. SQL Server: Immediate Checks, No Deferral

SQL Server checks FKs per statement and has no DEFERRABLE option. It also doesn’t support merge_action() in OUTPUT.

Why not parent-first in SQL Server?

• Because FKs are immediate, you cannot delete the parent before the children.
• Either delete junction rows first, or allow ON DELETE CASCADE (if permitted).
  

---

5. Decision Guide

PostgreSQL 17
✅ Use MERGE ... RETURNING + merge_action() for clear, auditable syncs
✅ Natural “author-first” logic in a single statement
✅ Best fit when cascades are forbidden

SQL Server
❌ No deferral, no merge_action() in RETURNING
✅ Must delete junction rows first (or rely on cascade)
✅ Still manageable with MERGE + OUTPUT

---

6. Summary

PostgreSQL’s deferred constraints and new MERGE RETURNING features enable clean, auditable synchronization of many-to-many relationships — even when ON DELETE CASCADE isn’t allowed.

SQL Server enforces constraints immediately, so you must stick to child-first deletes or cascades.If your business requires explicit control, PostgreSQL 17 delivers the best of both worlds: strong integrity and natural, developer-friendly workflows.