From: | James Gregory <james(at)anchor(dot)net(dot)au> |
---|---|
To: | PostgreSQL general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Referential Integrity problem |
Date: | 2003-03-19 11:00:27 |
Message-ID: | 1048071626.30665.13.camel@pirate.bridge.anchor.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Moore-Oliva | 2003-03-19 11:02:25 | Re: Division of intervals. |
Previous Message | Bob prograsp | 2003-03-19 10:44:56 | Oracle to Postgresql conversion |