Constraints and inheritance

I am currently designing a database that I expect to put into use after
6.4 is released.  This makes use of inheritance, and I would like to
ask about how inheritance will relate to the handling of constraints.

1. I would like to be able to say:

     create table job
       (
          ...
          resid char(4) not null references resource*(id),
          ...
       )

   to indicate that the foreign key constraint would be satisfied by the
   presence of the desired item in any class of the inheritance tree starting
   at resource.  The parser does not recognise this syntax at present.
   (This is parallel to `select ... from class*', by which we can currently
   list all items in an inheritance tree.)

2. Will all constraints on a class be inherited along with the column
   definitions?

   If constraints are inherited, there is the possibility of conflict or
   redefinition.

   In single inheritance, could a constraint be redefined by being restated
   in the descendent?

   In multiple inheritance, a conflict of column types causes an error; how
   will a conflict of constraint names be handled, if the check condition
   is different?  (Options: error; drop the constraint; require a new
   definition of the constraint in the descendent class.)

   At the moment, check constraints are inherited and are silently mangled
   by prefixing the class name; this can lead to an impossible combination
   of constraints, which could be solved if redefinition were possible.

   Example:

junk=> create table aa (id char(4) check (id > 'M'), name text);
CREATE
junk=> create table bb (id char(4) check (id < 'M'), qty int);
CREATE
junk=> create table ab (value money) inherits (aa, bb);
CREATE
junk=> insert into ab values ('ABCD', 5);
ERROR:  ExecAppend: rejected due to CHECK constraint aa_id
junk=> insert into ab values ('WXYZ', 5);
ERROR:  ExecAppend: rejected due to CHECK constraint bb_id

   We could perhaps allow syntax such as:

     create table ab (..., constraint id check (id > 'E' and id < 'Q'))
        inherits (aa, bb)
        undefine (constraint aa_id, constraint bb_id)

   Is this feasible?

   At present, primary key definitions are not inherited. Could they be?
   (either to share the same index or have a new one for each class, at
   the designer's option.)



--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver

PGP key from public servers; key ID 32B8FAA1

                 ========================================
    Come to me, all you who labour and are heavily laden, and I will
    give you rest. Take my yoke upon you, and learn from me; for I am
    meek and lowly in heart, and you shall find rest for your souls.
    For my yoke is easy and my burden is light.    (Matthew 11: 28-30)