Thread: Question about inheritance
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
"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
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.
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:> > Am I misunderstanding how the mechanism works, or isthis a big, noteasily> > solved, problem?>> The latter. Check the list archives for previous debates about this.> It'snot real clear whether an inherited primary key should be expected> to be unique across the whole inheritance tree, oronly unique per-table> (IIRC, plausible examples have been advanced for each case). If we want> uniqueness across multipletables, it'll take considerable work to> create an index mechanism that'd enforce it.>IMHO current behaviour of PostgreSQLwith inherited PK, FK, UNIQUE is simplybug not only from object-oriented but even object-related point of view. NowI can violate parent PK by inserting duplicate key in child! Inherited tables should honours all constraints from parent. If I changesome constraint (seems only FK, but not PK or UNIQUE)I should be able to doit in more restrictive manner. For example, two base table is connected viaFK. I can change such FK in childs from base1->base2to child1->child2 (orchild3) but not to child1->not_inherited_from_base2. CHECK, DEFAULT, NOTNULL are more freeto changes, isn't it? IMHO last message in doc/TODO.details/inheritance from Oliver Elphick is agood direction for implementing with exceptionon more rectrictive child FKconstraint (p.3 of message). As for me, I was pushed to rollback to scheme with no inheritance at all inmy project for now. So I'm very interesting inimplementing of rightinheritance and I wanted to ask similar question in one of the lists in nearfuture. Regards,Dmitry
>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? You would either have to check each index in the hierarchy or else have a single index across the whole hierarchy and check that. Obviously the latter would be generally more useful. As with all things inheritance, it is usually the right thing, and a good default that things be inherited. So ideally, indexes should work across whole hierarchies as well as primary, unique and foreign constraints. It could be argued that not inheriting is of very limited usefulness.
I have added this thread to TODO.detail/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. > > > IMHO current behaviour of PostgreSQL with inherited PK, FK, UNIQUE is > simply > bug not only from object-oriented but even object-related point of view. > Now > I can violate parent PK by inserting duplicate key in child! > > Inherited tables should honours all constraints from parent. If I change > some constraint (seems only FK, but not PK or UNIQUE) I should be able to > do > it in more restrictive manner. For example, two base table is connected via > FK. I can change such FK in childs from base1->base2 to child1->child2 (or > child3) but not to child1->not_inherited_from_base2. CHECK, DEFAULT, NOT > NULL are more free to changes, isn't it? > > IMHO last message in doc/TODO.details/inheritance from Oliver Elphick is a > good direction for implementing with exception on more rectrictive child FK > constraint (p.3 of message). > > As for me, I was pushed to rollback to scheme with no inheritance at all in > my project for now. So I'm very interesting in implementing of right > inheritance and I wanted to ask similar question in one of the lists in > near > future. > > Regards, > Dmitry > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026