Re: Constraints and inheritance - Mailing list pgsql-hackers

Last week, I wrote this, but no-one has answered. :(
  >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.)
  >


Having thought a bit more about it, I feel that all constraints should be
inherited, including primary key, and that the primary key index should
serve the entire tree, so that `select key from foo*' can be guaranteed
not to contain any duplicates.  Where there are non-identical constraints
of the same name, there ought to be a method for selecting the one to use
and also a method of redefining the constraint. (These ideas are taken
from the Eiffel language.)

  CREATE TABLE foobar (<field_definitions_and_constraints>)
    INHERITS (foo, bar)
    CHOOSE (constraint foo_id)
    REDEFINE (constraint id check (...))

The CHOOSE clause says that constraint foo_id is to be used in preference
to bar_id; the REDEFINE clause allows id (which would otherwise be foo_id,
from the CHOOSE clause) to be redefined as something new.

The CHOOSE clause would only be valid if there were constraints of the
same name in more than one ancestor.  If the constraints were actually the
same, it would be legal but unnecessary.

The REDEFINE clause would be legal if any ancestor contained the redefined
constraint.

I am thinking only of check constraints, at the moment.  As I said above,
I think that the primary key should apply to all descendants, using the same
index.  I am not sure about foreign keys, NOT NULL and UNIQUE.

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