Foreign key to multiple tables via forwarding table

Question

Problem statement

I have a table with a column whose values are foreign keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value alone, and there is a small, fixed set of such tables.

I'd like to add a foreign key constraint here so that my DBMS can ensure referential integrity. Of course, I can't do this directly, but I have a proposed solution that involves an intermediate "forwarding table" with incoming and outgoing foreign key constraints. I'm looking for review on:

• whether this solution in fact solves the problem, or if I missed an edge case;
• how this solution may fare in the face of changes to the data model (e.g., new referent tables);
• whether this use of Postgres GENERATED ALWAYS AS ... STORED columns is reasonable or suspect;
• whether this solution is likely to introduce concurrency issues.

Proposed solution

To illustrate the solution, consider a simple database that stores "users" and "groups". Users and groups are each keyed by integer IDs, and some bits of the ID are reserved to tell what kind of ID it is:

-- User and group IDs are both integers, but are in disjoint subsets of the key
-- space, distinguished by the low 8 bits.
CREATE DOMAIN userid AS int8 CHECK ((VALUE & 255) = 1);
CREATE DOMAIN groupid AS int8 CHECK ((VALUE & 255) = 2);

CREATE TABLE users(
    user_id userid PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE groups(
    group_id groupid PRIMARY KEY,
    admin userid NOT NULL REFERENCES users
);

INSERT INTO users(user_id, name) VALUES (1, 'alice'), (257, 'bob');
INSERT INTO groups(group_id, admin) VALUES (2, 1), (258, 1);

Now, both users and groups can create invoices. Invoices have entirely the same data whether they're created by a user or a group, so we just use a single table that stores the ID of the "actor" (user or group) that created the invoice along with the extra data:

-- Invoices can be created by either users or groups: collectively, "actors".
CREATE DOMAIN actorid AS int8 CHECK ((VALUE & 255) IN (1, 2));
CREATE TABLE invoices(
    actor actorid NOT NULL,
    create_time timestamptz NOT NULL,
    amount_cents int NOT NULL
);

Now, semantically, invoices.actor is a foreign key onto either users or groups, depending on the value of actor & 255. There's no way to directly write a REFERENCES constraint for that. We can imagine defining a view of all the actor IDs—

CREATE VIEW all_actor_ids AS (
    SELECT user_id AS actor FROM users
    UNION ALL
    SELECT group_id AS actor FROM groups
);

—such that, in principle, actor actorid REFERENCES all_actor_ids, but Postgres does not actually allow referring to views in foreign keys.

To work around this, we basically materialize all_actor_ids into a table that itself has foreign key constraints to ensure its own integrity:

CREATE TABLE actors(
    actor actorid PRIMARY KEY,
    user_id userid
        REFERENCES users
        GENERATED ALWAYS AS (CASE WHEN (actor & 255) = 1 THEN actor END) STORED,
    group_id groupid
        REFERENCES groups
        GENERATED ALWAYS AS (CASE WHEN (actor & 255) = 2 THEN actor END) STORED,
    CONSTRAINT actors_exactly_one_key
        CHECK (1 = (user_id IS NOT NULL)::int + (group_id IS NOT NULL)::int)
);

Now, invoices.actor can refer to actors:

ALTER TABLE invoices ADD FOREIGN KEY (actor) REFERENCES actors;

The idea is that, before you add an invoice on behalf of an actor, you first run INSERT INTO actors(actor) VALUES($1) ON CONFLICT DO NOTHING. The generated columns take care of populating either user_id xor group_id, the foreign key constraints on those columns ensure that the underlying entity actually exists, and the conflict handler makes the operation a no-op if the actor has been used before.

For example, with the above definitions, these inserts work:

-- All users and groups can be populated as actors.
INSERT INTO actors(actor)
    SELECT user_id FROM users UNION ALL SELECT group_id FROM groups
    ON CONFLICT DO NOTHING;

-- Invoices can be created for either actors or groups.
INSERT INTO invoices(actor, create_time, amount_cents)
    VALUES (1, now(), 100), (258, now(), 200);

Note that the actors data never actually needs to be part of a JOIN in a read path. It exists only to coax the foreign key constraints into submission.

Questions

It seems to me that this solution should properly ensure referential integrity: in particular, a user or group can't be deleted without cascading down to delete any invoices created by that user or group. But I have some questions:

• Am I missing some edge case in which this solution does not actually ensure referential integrity?

• Suppose that invoices can now also be created by a third type of entity: say, robots. I think that I can alter the actorid domain to incorporate robotids, then add a new actors.robot_id column like the others and update the actors_exactly_one_key constraint. Are there lurking issues that I should be wary of here?

• I haven't used Postgres GENERATED ALWAYS AS ... STORED columns before, and I'm a little nervous that the default expression can't be changed at all after the fact. Does this seem like an appropriate use of generated columns, or would it be better to replace the generated columns with CHECK constraints that ensure the same values but require the user to provide them?

• Is the INSERT INTO actors(actor) ... ON CONFLICT DO NOTHING likely to introduce concurrency issues? (Or, are there any other glaring performance issues that I've missed?)

Any other feedback or reviews also warmly appreciated.

I'm using Postgres 12, but if the best solution here requires upgrading to Postgres 14, I'm open to it.

Answer 1

Aren't you overcomplicating yourself?

Much simpler:

Make 1 column for each possible FK, then add a CHECK constraint to make sure that only 1 column is not-NULL and the rest stay NULL (if you want only 1 FK, you may want more).

Also much simpler:

Add FKs to the other tables, the other tables will all have an FK to the same main table, this way the DB has no constraint on how many relationships there may be but this might be simple to do through application logic and comes with the benefit of a very robust-looking DB schema. (if your requirements about how many relationships there may be to the main table changes, you don't even have to run a migration).

Personally I prefer to avoid anything with the word materialize if possible.

Answer 2

I agree with Jose that you are overcomplicating the problem. I think you can write a simple trigger on the column you want the database to check its integrity. I mean something like

select ((select * from users where user_id = actor_id) or (select * from "groups" where group_id = actor_id))