Postgresql – “Flexible” foreign key constraint

database-designpostgresql

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.

Best Answer

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.

Related Question