On Tue, 5 Jun 2001, Christopher Kings-Lynne wrote:
> Hi guys,
>
> It's relatively straightforward to allow check constraints to be inherited -
> but is it really possible to ever do the same with primary, unique or even
> foreign constraints?
>
> ie. Say a table has a primary key and I inherit from this table. Since the
> primary key is an index on the parent table, I could just create another
> index on the child table, on the same column.
>
> However - because we are dealing with two separate indices, it should still
> be possible to insert duplicate values into the parent table and the child
> table shouldn't it? This means that when a query is run over the parent
> table that includes results from the child table then you will get duplicate
> results in a supposedly primary index.
>
> Similar arguments seem to apply to unique and foreign constraints. If you
> could use aggregate functions in check constraints - you'd have another
> problem. And if asserts were ever implemented - same thing...
>
> Am I misunderstanding how the mechanism works, or is this a big, not easily
> solved, problem?
It's a big deal. Actually check constraints have a similar problem if you
allow inherited constraints to be dropped. "Why does 'select * from
base;' give me rows where value<10 since there's a check value>=10
on the table?"
As Tom said, the unique constraint thing is still questionable which is
the more meaningful semantics. If we ever want to allow foreign key
constraints to inheritance trees, we need *some* way to guarantees
uniqueness across the tree even if that isn't through the unique
constraint.