Re: Referential Integrity problem

On 19 Mar 2003, James Gregory wrote:

> On Wed, 2003-03-19 at 00:08, Stephan Szabo wrote:
> > On 19 Mar 2003, James Gregory wrote:
> >
> > > I hope this one is just some misunderstanding on my part.
> >
> > Referential integrity constraints currently apply only to the explicitly
> > named table.  In addition, the saleable_item primary key on id is not
> > inherited by product (and so there can be duplicates in product - even if
> > you put a unique constraint on product(id), you still can have duplicates
> > between saleable_item and product).
>
> Ar. Is there a way to do what I need to do? No insertions should ever
> occur in the "supertable" - is the best way forward to write a trigger
> that just tests if the id exists in the supertable? With this assertion
> that no inserts will occur in the supertable, is it sufficient to
> qualify my references to say saleable_item.id?

No, because the triggers still are only going to reference explicitly
saleable_item.  I believe there's a somewhat complicated work-around using
a new table that contains just ids that is referenced by saleable_item,
product and chart_item with triggers for dealing with changes to
saleable_item and product.

> This behaviour seems inconsistent (to me anyway). Is it likely to
> change? Why isn't the primary key inherited?
>
> Am I correct then in my understanding that postgres's inheritance is
> merely a table templating system rather than inheritance, per se? - that
> is, it seems to me that if it were inheritance that storing a list of
> saleable_items and filling it with products should be entirely
> equivalent to storing a list of products. Why is this not so?

Inheritance needs alot of work.  There have been various discussions in
the past (see -general and -hackers archives), but I don't believe that a
plan that satisfied everyone was ever reached.