How do I enforce uniqueness across parent and child tables without denormalizing my data?

I have two tables in Postgres with a parent/child relationship:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL,
    rundate DATE NOT NULL
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    a_id INT NOT NULL REFERENCES table_a(id),
    number INT NOT NULL
);

Constraints:

In table_a, it is fine to have duplicate (type, rundate) combinations.

In table_b, number can repeat for different parents.

But across both tables together: for each unique (type, rundate) pair in table_a, I must not allow the same number to appear more than once in table_b.

In other words, I need to enforce uniqueness of (table_a.type, table_a.rundate, table_b.number).

Since Postgres doesn’t allow a unique index that spans two tables, not does it allow constraints to use multiple tables. What’s the best way to enforce this constraint?

I also considered denormalizing type and rundate into table_b so I could add a unique constraint there, but I’d rather avoid redundant data if possible.

What I expect:

I want the database to reject an insert/update into table_b if it would create a duplicate (type, rundate, number) across the parent/child relationship.

Solution

You could try to do it with triggers, but that introduces a race condition. Two sessions trying to insert the same values could check for a duplicate, find no duplicate, and both conclude that they will permit an INSERT.

A similar race condition exists if you try to do the check in application code before the INSERT.

You could resolve the race condition with table locking or user locking, but that creates a bottleneck.

You've already thought of the solution of making "redundant" copies of the values in table_b so you can enforce a unique constraint there. It may seem wasteful because of the extra copy of type and rundate, but I think that's the simplest way.

After all, any foreign key is arguably a redundant copy in the child table.

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL,
    rundate DATE NOT NULL,
    UNIQUE (id, type, rundate)
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    a_id INT NOT NULL REFERENCES table_a(id),
    type TEXT NOT NULL,
    rundate DATE NOT NULL,
    number INT NOT NULL,
    FOREIGN KEY (a_id, type, rundate) REFERENCES table_a(id, type, rundate),
    UNIQUE (type, rundate, number)
);

INSERT INTO table_a VALUES 
(123, 'type1', '2025-09-01'),
(133, 'type1', '2025-09-01');

-- OK
INSERT INTO table_b VALUES
(1, 123, 'type1', '2025-09-01', 42);

-- OK
INSERT INTO table_b VALUES
(2, 123, 'type1', '2025-09-01', 43);

-- NOT OK
INSERT INTO table_b VALUES
(3, 123, 'type1', '2025-09-01', 42);