Foreign key to all inherited tables

On Tue, 22 Aug 2000, Darrin Ladd wrote:

Hi,

I was wondering if there is a way to have a foreign key reference to the
primary key column of all tables throughout an inheritance tree. For
example, I have a parent_table with a unique_id (type serial) and a child
table which inherits the parent_table (inheriting the unique_id). I would
like to have another table have a field, unique_id, who's value must be in
the unique_id field of the parent or the child. I tried adding an asterix
to the end of the foreign key table refrence, {CONSTRAINT fk_other_table
FOREIGN KEY (unique_id) REFERENCES parent_table* (unique_id)} but the parser
didn't like that. Then I tried creating a check constraint on the field in
the 'other_table' to check if the value was 'IN (SELECT unique_id from
parent_table*)'. The table creation went fine, but when I tried to insert
any values into the table it produced an error:
ExecEvalExpr: unknown expression type 108.

Currently you cannot do Foreign Keys to inheritance tress (as you noted),
that's in the known things to do to the foreign key stuff, but doesn't
have a particular ETA. The latter thing is a problem with subselects in
constraints which is a not particularly easy thing to deal with, since
such constraints are actually on all tables referenced in the subselect
as well as the table you specified the constraint on.

You may be able to do this with triggers. You'd technically need one for
insert/update on the main table and one for update/delete on each table of
the inheritance tree (to prevent deletions of referenced items). This
isn't a complete soulution really (there are some details of FK that are
a bit wierd and hard to do in normal triggers, but it's probably fairly
close)