"Flexible" foreign key constraint

Question

We have a Postgres setup with multiple tables representing different parts and stages of a project, 4 of them at the moment but potentially more in the future. We want to make a Task table with a foreign key to any of these tables. Preferably like this:

CREATE TABLE stage1(
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage2 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE stage4 (
id SERIAL PRIMARY KEY,
description TEXT);

CREATE TABLE task (
id SERIAL PRIMARY KEY,
description TEXT,
foreignkey_table VARCHAR(20),
foreignkey_id INTEGER);

where the column named foreignkey_table can indicate the "parent" table and the foreignkey_id will indicate the "parent id".

These stages 1 to 3 are to make the example simple but they can in no way be joined as one. Think of them as "preparation stage", "execution stage" and "evaluation stage". Or as completely different tables like "users", "companies" and "activities" each with its own specific not overlapping content.

How can I do this?

I have read some posts telling it is not possible but they were quite old so I'm hoping a solution is implemented after these posts. I also found a potential solution on Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL), and this is my current best fallback option.

Answer 1

I don't see the need for multiple stage tables at all (tip: if you have increasing numbers on columns or tables you probably violated normalization rules).

I would create a single stage table that contains the information which stage that is:

CREATE TABLE stage
(
  id SERIAL PRIMARY KEY,
  level integer not null check (level between 1 and 4),
  description TEXT
);

CREATE TABLE task 
(
  id SERIAL PRIMARY KEY,
  description TEXT,
  stage_id integer references stage
);

If you get new stages in the future, all you need to do is add new rows to the stage table.

Answer 2

I'm in no position to judge if the desired design you try to achieve is proper or desirable. To me it looks like it will not be rated as a clean table design.

This proposal uses a base-table which provides a relation for the IDs a stage can have. No matter what stage an entry belongs to, the ID is stored in a table called STAGE_BASE. The table might look similar to

create table STAGE_BASE (ID serial primary key);

Your single stages could be created as

create table STAGE1(fk_id INTEGER references STAGE_BASE, description TEXT);
create table STAGE2(fk_id INTEGER references STAGE_BASE, description TEXT);

where fk_id is a foreign key to STAGE_BASE(ID).

The task table stays the same as you proposed, but the foreign key now references to the same ID as all STAGE tables:

CREATE TABLE task (
  id SERIAL PRIMARY KEY,
  description TEXT,
  foreignkey_table VARCHAR(20),
  foreignkey_id INTEGER references STAGE_BASE);

Instead of a VARCHAR(20) data type for the column foreignkey_table you could use an INTEGER as stage number, which could be used in conjunction with yet another table, e.g. STAGES where the stage number references via foreign key constraint to this table.

Note that this is not a clean solution, and the query-statements you will have to write bear witness of the mess you will create with this table design. This is most obvious in the select-statements because there is no clean and automatic way of joining all information together based on the contents of the table TASK. You could join all stages via outer-join, though, but it still is not performing well and still is bad designed.

On the other hand, if you know in each stage which stage-table you have to join, and this is fixed (not dynamic) during program execution, you might have a not beautiful but working solution.