Thread: Referential Integrity problem
I hope this one is just some misunderstanding on my part.
I have a table structure like so (scroll down for executive summary):
create table common (
createdate timestamp default current_timestamp,
modifydate timestamp default current_timestamp,
syncdate timestamp default '1900-01-01'
);
create table saleable_item (
id serial primary key,
sale_id integer references sale,
stock integer default 0,
baseprice float default 0.00
) inherits (common);
create table product (
title_id integer references title,
format_id integer references format
) inherits (saleable_item);
create table chart (
id serial primary key,
name text
) inherits (common);
create table chartitem (
id serial primary key,
chart_id integer references chart,
position integer,
product_id integer references saleable_item
) inherits (common);
That's the minimum I can show you to demonstrate this unfortunately. So,
there is a product table that inherits a saleable_item table,
saleable_item defines fields relating to stuff that you'd sell (products
is a poorly named table, but it's for historical reasons). saleable_item
in turn inherits from common - which defines some fields I need to store
about every record - in particular information to help me syncronise
this database with another one.
The chart and chartitem table are meant to represent listings of
products or rather, saleable_items that my client wants to draw
attention to. So you put an entry into chart to give this collection of
products a descriptive name, and then each item in the chart refers to
this chart by its ID.
Here's the problem:
# insert into chartitem (chart_id, position, product_id) values
(1073741826, 1, 1073741827);
ERROR: $2 referential integrity violation - key referenced from
chartitem not found in saleable_item
the product ID does exist:
# select id from product where id = 1073741827;
id
------------
1073741827
(1 row)
And of course it exists in saleable_item as well (since that is where
the ID field is inherited from).
Now I assume that something has gone wrong in inheriting the primary key
- is there anything I'm missing? I'm sure I've done this sort of thing
before.
# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.1
(Mandrake Linux 9.1 3.2.1-7mdk)
(1 row)
Many thanks,
James.
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).
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? 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? Thanks for your help, James.
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.
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.
On 19 Mar 2003, James Gregory wrote: > 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. > 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? You can give arguments at CREATE TRIGGER time and do stuff based on the arguments from there. You'll need to be careful to make sure all of the concurrency and modification of the pk side of the constraint works properly. As a note, you'll need triggers on saleable_item and product to deal with modifications to the pk-side of the constraint. The one on saleable_item isn't strictly necessary since you don't plan to modify it, but it's probably good from a consistancy stanpoint. You'll also need triggers on any other saleable_item subclasses.