Re: inherited columns as foreign keys WAS "no subject"

On Sat, 6 Apr 2002, Michael Adler wrote:

>
> On Fri, 5 Apr 2002, Stephan Szabo wrote:
>
> > > I get a "referential integrity violation", but the referenced key *does*
> > > exist in the referenced table.
> > >
> > > I don't think that it's important, but the table "object" is inherited by
> > > other tables.
> >
> > In fact it may certainly be... References constraints do not inherit
> > to children currently.  The constraint selects from only the named table
> > (do a select * from ONLY object where id=...) and I'd guess that the row
> > is actually in one of the children.
>
> I think the solution is to create a unique index on the child
> table so that you can reference the inherited column.

That only works if you only want to reference the child.  If you want to
reference a number of tables in the same hierarchy you're kind of out of
luck since all of the references constraints need to be satisfied.

One thing I've seen for that is to make another table that actually holds
the ids that would otherwise be inherited and have the tables in the tree
reference that one for their id (each table needs the constraint of
course).  Then all the other tables that want to reference the base can
reference the id table instead.  This is alot more work in some ways
because now you have another table that you have to modify.  Of course,
since primary keys don't inherit either, it actually allows you to get
a unique constraint across the ids as well...

> In that "gee, it would be nice" category of suggestions, I'd like to see
> more documentation on inheritance, it's limitations and suggested
> workarounds. This has probably all been said before..

That's probably a good idea, but nobody's ever stepped up to do it, mostly
because inheritance needs alot of work and everyone's hoping that someone
will fix all these cases.

> The interactive docs on inheritance suggest one workaround for inherited
> columns as foreign keys: use a "CHECK" constraint with a custom function
> instead of REFERENCES constraint. Without much investigation, it seems
> like a better idea to create a unique index on the child table and
> REFERENCE that. This seems simpler to setup and affords more integrity
> features. I haven't really tried it, though.

The check constraint is incomplete as well without triggers on each of the
tables of the inheritance tree to prevent delete/update (or do referential
actions), but it's the easiest way to get part of the implementation.