Set one column in a table as foreign key to two target tables

Question

I have 3 tables in PostgreSQL 9.6:

**table1 :** {id( primary key) ,account_id}
**users  :** {id( primary key)} INHERITS (common_table);
**channel:** {id( primary key)} INHERITS (common_table);

I want something like this:

FOREIGN KEY (account_id) REFERENCES (users(id) OR channel(id)) ON UPDATE CASCADE ON DELETE CASCADE

What is the best way?

Erwin Brandstetter · Answer 1 · 2017-12-12T13:21:36.133

Not possible. This is a known limitation of Postgres inheritance.

Read the chapter Caveats in the manual, which closes with the statement:

There is no good workaround for this case.

Addressing your case exactly.

If you need an FK constraint enforcing referential integrity like this, you have to ditch inheritance. You could instead have table1 as a "master" table holding the complete key space in its id column. And users as well as channel have their respective id as FK to table1.id.

Possibly add a type column to table1, a (redundant) type column in users and channel and use a multicolumn FK constraint, so that rows in users can only reference type "user" in table1, etc.

Your desired FK constraint could then just be:

FOREIGN KEY (account_id) REFERENCES table1(id) ON UPDATE CASCADE ON DELETE CASCADE

Set one column in a table as foreign key to two target tables

1 Answers1