Uniqueness across two tables: a PostgreSQL trigger and a safe batch update

The problem

In the configurator engine, many things are driven by conditions: show this option if the width is above 120 and the material is oak, or if the finish is "premium". A condition is a small logic tree with two kinds of nodes:

• operations: logical nodes like AND / OR that group other nodes,
• comparisons: leaf nodes like width > 120 or material = oak.

Like the item tree (see "Reordering a tree without breaking unique constraints"), condition nodes are positioned with a termnum and point to their parent with a parentTermnum. For the tree to be rebuilt correctly, a termnum must identify one node inside its condition.

Here's the catch: operations and comparisons are different tables, because they have different columns. But they share one numbering space:

condition 7
├── termnum 1  operation   AND
│   ├── termnum 2  comparison  width > 120
│   └── termnum 3  comparison  material = oak

If an operation and a comparison in the same condition both had termnum = 2, a child pointing to parentTermnum = 2 would be ambiguous, and the condition could evaluate wrongly. That means wrong products shown, or wrong options allowed.

Each table could have its own UNIQUE (condition_id, termnum), and they do. But nothing stopped a duplicate between the two tables.

The details

Why UNIQUE can't span tables

A PostgreSQL UNIQUE constraint (or index) belongs to one table. There is no built-in "unique across these two tables" constraint. The usual options are:

1. Merge the tables into one condition_nodes table with nullable columns for each kind. That gives a single constraint, but a wide, loosely-typed table and a big migration.
2. A shared sequence or registry table: every node first reserves its number in a common table that has the unique constraint. That's robust, but it adds a write and a join to everything.
3. A trigger: before inserting into either table, check the other one.

The tables and their code were already in production and used everywhere, so a trigger was the least disruptive option. It enforces the rule inside the database, where every writer must pass through it.

The challenges

The trigger must look at both tables

When inserting an operation, the trigger must check existing operations and existing comparisons in the same condition. Same for comparisons. One function, attached to both tables.

Reordering is a multi-table swap

Users also reorder condition nodes. A reorder can move operations and comparisons at the same time, often swapping numbers between the two kinds. Row-by-row updates create temporary duplicates (the same problem as swapping items), and now the duplicates can be across tables, which deferrable constraints can't cover.

Conflicts can come from three places

When a batch of new numbers arrives, a duplicate can be:

1. inside the batch itself (two nodes asked for the same number),
2. against operations that are not being updated,
3. against comparisons that are not being updated.

Each needs its own check, with a clear error.

Migrations and fresh databases

Like the other custom SQL, the trigger had to be recreated reliably on every environment, including Prisma's temporary shadow database used during migrations, without failing because it already existed.

The solution

1. One trigger function for both tables

DROP TRIGGER IF EXISTS check_operation_position ON operations;
DROP TRIGGER IF EXISTS check_comparison_position ON comparisons;
DROP FUNCTION IF EXISTS check_condition_position_unique();

CREATE OR REPLACE FUNCTION check_condition_position_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM operations  WHERE condition_id = NEW.condition_id AND termnum = NEW.termnum
    UNION ALL
    SELECT 1 FROM comparisons WHERE condition_id = NEW.condition_id AND termnum = NEW.termnum
  ) THEN
    RAISE EXCEPTION 'Termnum % already exists in condition %', NEW.termnum, NEW.condition_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_operation_position
  BEFORE INSERT ON operations
  FOR EACH ROW EXECUTE FUNCTION check_condition_position_unique();

CREATE TRIGGER check_comparison_position
  BEFORE INSERT ON comparisons
  FOR EACH ROW EXECUTE FUNCTION check_condition_position_unique();

A few deliberate choices:

• BEFORE INSERT: the check runs before the row exists, and raising an exception cancels the insert (and the surrounding transaction).
• UNION ALL + EXISTS: PostgreSQL stops at the first match; no counting, no sorting.
• The same function for both tables: one definition of the rule, so the two tables can't drift apart.
• DROP … IF EXISTS first: the script is safe to re-run on any database, including the shadow database during migrations.
• A readable message: Termnum 2 already exists in condition 7 tells a developer exactly what collided.

The per-table UNIQUE (condition_id, termnum) constraints stay in place too. They're cheap, fast, and cover same-table duplicates even in paths the trigger doesn't.

2. Why the trigger only guards inserts

The trigger fires on INSERT only. That's intentional: if it also fired on every UPDATE, a legitimate swap between an operation and a comparison would be rejected at the first row, because of the temporary duplicate. So position changes don't go through ad-hoc updates at all. They go through one dedicated function that validates the whole batch up front.

3. A batch reorder function with validation phases

The reorder function receives two JSON arrays, the new positions for operations and for comparisons:

SELECT apply_condition_updates(
  '[{"id": 10, "newTermnum": 3}]'::jsonb,   -- operations
  '[{"id": 21, "newTermnum": 1}]'::jsonb    -- comparisons
);

Inside, it works in clear phases (simplified):

-- Collect every requested change, from both tables, in one temp table
CREATE TEMP TABLE pending (id BIGINT, kind TEXT, condition_id INT, new_pos INT) ON COMMIT DROP;
-- …insert rows for operations and comparisons…

-- Phase 1: duplicates inside the batch itself
IF EXISTS (SELECT 1 FROM pending GROUP BY condition_id, new_pos HAVING COUNT(*) > 1) THEN
  RAISE EXCEPTION 'Duplicate termnum inside the update batch';
END IF;

-- Phase 2: collisions with operations that are NOT being moved
-- Phase 3: collisions with comparisons that are NOT being moved
--   (same idea: join pending.new_pos against rows whose id is not in pending)

-- Step 1: park every moving node on a temporary negative position
UPDATE operations o  SET termnum = -o.id FROM pending p WHERE p.kind = 'op'   AND p.id = o.id;
UPDATE comparisons c SET termnum = -c.id FROM pending p WHERE p.kind = 'comp' AND p.id = c.id;

-- Step 2: move them to their final positions
UPDATE operations o  SET termnum = p.new_pos FROM pending p WHERE p.kind = 'op'   AND p.id = o.id;
UPDATE comparisons c SET termnum = p.new_pos FROM pending p WHERE p.kind = 'comp' AND p.id = c.id;

Why it's safe:

• All conflicts are detected before anything moves. Phases 1–3 prove the final state is valid: unique within the batch, and unique against every node that stays where it is, in both tables.
• Negative parking avoids temporary collisions. Real positions are always positive, so moving each node to a unique negative value (like -id) first frees every target position. The second step can then assign final values without ever touching an occupied number.
• It all runs in one transaction. Any exception rolls everything back. The condition is either fully reordered or untouched.

4. Shipped with the migration pipeline

Like the deferrable constraints, these functions live in the ordered functions/ folder and are applied after every migration by the same idempotent runner. A fresh database, a CI database and production all end up with the same trigger and the same function, automatically.

Lessons learned

• Some rules don't fit in one table. When an invariant spans tables, decide consciously where it lives: merged table, registry table or trigger. Don't just hope the application code never forgets.
• Triggers are a good fit for "check before write" rules that every writer must respect, including scripts and future code you haven't written yet.
• Know exactly which statements your trigger guards. Guarding inserts only, and routing every reorder through one validated function, avoided rejecting legitimate swaps.
• Validate the whole batch before changing anything. Three clear checks (inside the batch, against table A, against table B) give precise errors and protect the data.
• Park, then place. Moving rows to a guaranteed-free range first (negative numbers) is a simple, reliable way to swap values without temporary collisions.
• Make database scripts re-runnable. DROP … IF EXISTS + CREATE OR REPLACE keeps migrations and shadow databases happy.
• Error messages are for humans. "Termnum 2 already exists in condition 7" saves a lot of debugging time.

FAQ

Can a UNIQUE constraint span two tables in PostgreSQL?

No. UNIQUE constraints and indexes apply to a single table. To enforce uniqueness across tables you can merge the tables, use a shared registry table with its own unique constraint, or use triggers that check the other table.

Are triggers safe for enforcing uniqueness?

They work well for many cases, but be careful with concurrency: two transactions inserting the same value at the same moment may both pass a trigger check before either commits. For high-concurrency writes, combine triggers with locking (for example locking the parent row) or use a registry table with a real unique constraint.

How do you swap values between rows without violating uniqueness?

Move the rows to temporary values that can't collide (for example negative numbers), then move them to their final values, all inside one transaction. For single-table constraints, a deferrable constraint is another option.

What is a shadow database in Prisma?

A temporary database Prisma Migrate creates during development to detect schema drift and generate migrations. Custom SQL that runs against it must be idempotent, using IF EXISTS / IF NOT EXISTS and CREATE OR REPLACE.