Thread: BUG #3417: Foreign key constraint violation occurs unexpectedly

The following bug has been logged online:

Bug reference:      3417
Logged by:          David Boesch
Email address:      davidboesch@datasc.com.au
PostgreSQL version: 8.2.4
Operating system:   Linux Redhat Linux linux2 2.4.20-8 #1 Thu Mar 13
17:54:28 EST 2003 i686 i686 i386 GNU/Linux
Description:        Foreign key constraint violation occurs unexpectedly
Details:

I have created a table with a surrogate primary key like so.

create table reference(
        id              serial primary key,
        name            varchar(50) not null,
        description     varchar(50)
);

Then I create a table which references the primary key like so.

create table a(col1 integer references reference(id));

I add data to reference

select * from reference shows as

 id |      name      |          description
----+----------------+--------------------------------
 11 | rd             | road
 12 | st             | street
 13 | way            | way
 14 | close          | close
 15 | bend           | bend
  3 | vic            | victoria
  4 | nsw            | new south wales
  5 | qld            | queensland
  6 | nt             | northern territory
  7 | sa             | south australia
  8 | tas            | tasmania
  9 | wa             | western australia
 10 | act            | australian captial territory
 16 | nab            | national australia bank
 17 | cba            | commonwealth bank of australia
 18 | anz            | bank of new zealand
 19 | westpac        | westpack banking corporation
 20 | bqld           | bank of queensland
 21 | mqb            | macquarie bank
 22 | suncorp        | suncorp
  1 | unit           | unit
  2 | lot            | lot
 23 | assets         | assets
 24 | liabilities    | liabilities
 25 | equity         | equity
 26 | income         | income
 27 | cost of sales  | cost of sales
 28 | expenses       | expenses
 29 | other income   | other income
 30 | other expenses | other expenses
 31 | au             | australia
 32 | usa            | United States of America
 33 | oakleigh       | oakleigh
 34 | st albans      | st albans


When inserting into a with the following

dsc=# insert into a (col1) values(7);
ERROR:  insert or update on table "a" violates foreign key constraint
"a_col1_fkey"
DETAIL:  Key (col1)=(7) is not present in table "reference".
STATEMENT:  insert into a (col1) values(7);
ERROR:  insert or update on table "a" violates foreign key constraint
"a_col1_fkey"
DETAIL:  Key (col1)=(7) is not present in table "reference".


Why do I get a foreign key constraint violation when I have the id of 7 in
the table?

I must be missing something here.

"David Boesch" <davidboesch@datasc.com.au> writes:

> I add data to reference
>
> select * from reference shows as
>
>  id |      name      |          description
> ----+----------------+--------------------------------
>  11 | rd             | road
>  12 | st             | street
>  13 | way            | way
>  14 | close          | close
>  15 | bend           | bend
>   3 | vic            | victoria
>   4 | nsw            | new south wales
>   5 | qld            | queensland
>   6 | nt             | northern territory
>   7 | sa             | south australia

How did you add this data? Given that the ids are out of order I assume you've
updated or deleted and re-inserted records a few times? That shouldn't break
anything but it's possible the insert on table a doesn't see the same version
of this table that you're looking at with the select.

Also, just to check that there's nothing wrong with the inex, what do you get
if you do:

enable_seqscan = off;
select * from reference where id = 7;

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Gregory,

Thanks for the reply.

The data has been added via a file, as it is static data, no user input
for this table, and yes I have run the file many times.

I have accidently left some information out however that I believe will
probably add some light on this. The data has been added to this table
via an inherited table. Reference is the parent or base table, the
region table is the child table, I have inserted records into the region
table, and hence they go into the base table also. I never specify the
id column in my inserts it is a serial column.

I have since read some doco that postgres does not support referential
integrity with inherited tables (shame this, I love the feature), but
was'nt sure under what circumstance.

This is most likely why this is not working. This is perhaps not a bug
after all, appologies.

Tried your query with set enable_seqscan=off and then the result of the
query brings back the one row as expected.

I'll search around and try to find when and if inhertance will be
supported with RI.

Thanks again.

Regards

David












On Thu, 2007-06-28 at 22:48, Gregory Stark wrote:
> "David Boesch" <davidboesch@datasc.com.au> writes:
>
> > I add data to reference
> >
> > select * from reference shows as
> >
> >  id |      name      |          description
> > ----+----------------+--------------------------------
> >  11 | rd             | road
> >  12 | st             | street
> >  13 | way            | way
> >  14 | close          | close
> >  15 | bend           | bend
> >   3 | vic            | victoria
> >   4 | nsw            | new south wales
> >   5 | qld            | queensland
> >   6 | nt             | northern territory
> >   7 | sa             | south australia
>
> How did you add this data? Given that the ids are out of order I assume you've
> updated or deleted and re-inserted records a few times? That shouldn't break
> anything but it's possible the insert on table a doesn't see the same version
> of this table that you're looking at with the select.
>
> Also, just to check that there's nothing wrong with the inex, what do you get
> if you do:
>
> enable_seqscan = off;
> select * from reference where id = 7;
--
David Boesch
0410452873
95633008
9 Ferntree Gully Rd Oakleigh 3166