Re: Referential Integrity problem

On Wed, 2003-03-19 at 00:47, Stephan Szabo wrote:
> 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.

Ok, so, it seems to me that what I need to do is create trigger
functions to implement this check. They will basically consist of a
check to maintain referential integrity with this inherited structure,
and throw an exception if the proposed modification would break that.
Then, assuming all the checks passed and no exception was thrown it
would simply return the new row.

So I would need one trigger for inserts and updates, and another for
deletes.

So at this point I have two options - write a script to write a
different trigger function for each inherited table, or, use some
postgres magic in this trigger function to infer what table the new
record is intended for, and then query the system tables to do the right
thing.

The script sounds like a bad idea, just from a maintenance standpoint.
So, is there a way to infer what I need to know from within a trigger?
Alternatively, can I specify the table name as a parameter, and use that
to do the system table queries?

Thanks,

James.