Re: Foreign Key work for 7.3+

On 20 Feb 2002, Oliver Elphick wrote:

> On Wed, 2002-02-20 at 17:28, Stephan Szabo wrote:
> > Here are the things I'd like to get feedback on doing to the foreign
> > key constraint triggers and support code. ...
> >
> > * Extend fk constraints to work with inheritance
> > Make fk constraints inherit properly with both fk and pk base tables.
> > This will probably mean making the appropriate triggers on the child
> > tables involved as well as scanning the additional tables when checks and
> > changes are needed. For right now, I'd say we'd want to require that the
> > child tables at least also have unique constraints across the key.
>
> This means that we need a solution for unique indexes and primary keys
> under inheritance. I wrote about this in a mail which is preserved in

Yeah, I'm sort of hoping that someone who works more with inheritance
will jump on the primary keys thing so that I can utilize it from the
foreign key constraints. This one's one of the parts likely to be put
off since as much as I'd like to get it done it sounds like it's going
to take a lot of work.

> doc/TODO.detail/inheritance.
>
> 3. Inheritance of a table implies inheriting all its constraints
> unless ONLY is used or the constraints are subsequently dropped;
> again, dropping operates through all descendant tables. A primary
> key, foreign key or unique constraint cannot be dropped or modified
> for a descendant. A unique index on a column is shared by all
> tables below the table for which it is declared. It cannot be
> dropped for any descendant.
>
> In other words, only NOT NULL and CHECK constraints can be dropped
> in descendants.
>
> [On reconsidering this, I'm not sure it is right to let these be
> modified either.]

I'm not sure either since a non-ONLY select on the parent will show the
rows that wouldn't meet the parent's constraints.

> 4. RI to a table implies the inclusion of all its descendants in the
> check. Since a referenced column may be uniquely indexed further up
> the hierarchy than in the table named, the check must ensure that
> the referenced value occurs in the right segment of the hierarchy.
> RI to one particular level of the hierarchy, excluding descendants,
> requires the use of ONLY in the constraint.
>
> So an index must somehow be made to serve more than one table, and then
> an index lookup must also discover whether the key is in the right
> segment of the inheritance hierarchy:

I'm sort of assuming that a cross table index will contain some reference
to the table it came from if only to help provide a way to reference the
associate heap tuple row.