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?

Chris


Tue, 5 Jun 2001 09:42:38 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Question about inheritance
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Am I misunderstanding how the mechanism works, or is this a big, not easily
> solved, problem?

The latter.  Check the list archives for previous debates about this.
It's not real clear whether an inherited primary key should be expected
to be unique across the whole inheritance tree, or only unique per-table
(IIRC, plausible examples have been advanced for each case).  If we want
uniqueness across multiple tables, it'll take considerable work to
create an index mechanism that'd enforce it.

			regards, tom lane

Mon, 04 Jun 2001 22:07:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about inheritance 
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.


Mon, 4 Jun 2001 20:46:34 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Question about inheritance