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)
