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

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.

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..

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 other limitation that I didn't find in the main docs is that child
tables don't inherit triggers. You have to add a trigger for each child
table, although I was able to reuse the same function.

It would be great to have this info in one place in the docs.

I feel like including the obvious: PostgreSQL is spectacular. I can't
imagine using anything else in this problem space.

Mike Adler