The following bug has been logged online:
Bug reference: 3417
Logged by: David Boesch
Email address: [EMAIL PROTECTED]
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.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
