Re: Foreign Key work for 7.3+ - Mailing list pgsql-hackers

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
doc/TODO.detail/inheritance.
   3. Inheritance of a table implies inheriting all its constraints   unless ONLY is used or the constraints are
subsequentlydropped;   again, dropping operates through all descendant tables.  A primary   key, foreign key or unique
constraintcannot be dropped or modified   for a descendant.  A unique index on a column is shared by all   tables below
thetable for which it is declared.  It cannot be   dropped for any descendant.      In other words, only NOT NULL and
CHECKconstraints can be dropped   in descendants.
 

[On reconsidering this, I'm not sure it is right to let these be
modified either.]
   In multiple inheritance, a column may inherit multiple unique   indices from its several ancestors.  All inherited
constraintsmust   be satisfied together (though check constraints may be dropped).      4. RI to a table implies the
inclusionof all its descendants in the   check.  Since a referenced column may be uniquely indexed further up   the
hierarchythan 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:

         table_a (id INTEGER PRIMARY KEY, ...)         table_b (..., bfld CHAR UNIQUE) INHERITS (table_a)
table_c1(...) INHERITS (table_b)         table_c2 (...) INHERITS (table_b)         table_d (...) INHERITS (table_c1,
table_c2)
         table_x (a INTEGER REFERENCES table_a(id))  -- looks at whole
  -- hierarchy         table_y (a INTEGER REFERENCES table_b(id),  -- looks at whole
                -- hierarchy from                                                     -- b down,
                            -- excluding a                  b CHAR REFERENCES table_b(bfld),   -- looks at whole
                                            -- hierarchy from                                                     -- b
downfor bfld                  c CHAR REFERENCES ONLY table_b(bfld) -- excludes b's                  )
              -- descendants         table_z (a INTEGER REFERENCES table_c2(id)) -- looks at c2 and
                               -- d
 

Perhaps the answer to this is to create a separate key table for each
unique field, which would act as an indirect index:
     table_a_keys_id (id INTEGER PRIMARY KEY,                            table  OID REFERENCES pg_class(oid)
                          ON UPDATE CASCADE                                       ON DELETE CASCADE)
table_b_keys_bfld(id CHAR PRIMARY KEY,                            table  OID REFERENCES pg_class(oid)
                   ON UPDATE CASCADE                                       ON DELETE CASCADE)
 

So the key table would record which table contained each key, and it
would need triggers to update it whenever any rows in the hierarchy
changed.  There would not be a unique index on any of the tables
directly, but instead there would be unique indexes on the keys tables.

On the creation of the first descendant table in a hierarchy, the key
tables would be created and filled with any existing keys from the
ancestor table; the existing unique indexes of the ancestor would be
dropped. On dropping the last descendant in a hierarchy, the reverse
would have to be done.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "For the Lord himself shall descend from heaven with a      shout, with the voice of the archangel, and with the
 trump of God; and the dead in Christ shall rise first;     Then we which are alive and remain shall be caught up
togetherwith them in the clouds, to meet the Lord in      the air; and so shall we ever be with the Lord."
                I Thessalonians 4:16,17