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.