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)
