Skip site navigation (1) Skip section navigation (2)

BUG #2122: Inconsistent FK Error Messages

From: "David Wheeler" <david(at)justatheory(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2122: Inconsistent FK Error Messages
Date: 2005-12-23 04:47:07
Message-ID: 20051223044707.D0A31F0AC7@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2122
Logged by:          David Wheeler
Email address:      david(at)justatheory(dot)com
PostgreSQL version: 8.1.0
Operating system:   Mac OS X 10.4.3
Description:        Inconsistent FK Error Messages
Details: 

I noticed that when I update or insert a record that violates a foreign key
constraint, I get an error such as 'insert or update on table "b" violates
foreign key constraint "b_a_id_fkey"', but when I delete a record where the
primary key is referenced as a foreign key elsewhere, and the FK constraint
is RESTRICT, I get an erro such as 'update or delete on "a" violates foreign
key constraint "b_a_id_fkey" on "b"'. The bit that's inconsistent between
them is that the former says 'table "b"' while the latter just says
'"a"'--no 'table'.

Here's how to reproduce it:

sharky=# create table a (id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for serial
column "a.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for
table "a"
CREATE TABLE
sharky=# create table b(id serial primary key, a_id int references a(id));
NOTICE:  CREATE TABLE will create implicit sequence "b_id_seq" for serial
column "b.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for
table "b"
CREATE TABLE
sharky=# insert into a (id) values (nextval('a_id_seq'));
INSERT 0 1
sharky=# insert into b (a_id) values(currval('a_id_seq'));
INSERT 0 1
sharky=# insert into b (a_id) values(-1);
ERROR:  insert or update on table "b" violates foreign key constraint
"b_a_id_fkey"
DETAIL:  Key (a_id)=(-1) is not present in table "a".
sharky=# delete from a;
ERROR:  update or delete on "a" violates foreign key constraint
"b_a_id_fkey" on "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".
sharky=# 

No biggie, but I though it was worth knowing.

Thanks!

Responses

pgsql-bugs by date

Next:From: zhuge xiaoDate: 2005-12-23 07:25:16
Subject: Re: [BUGS] BUG #2118: could not connect to server
Previous:From: Tom LaneDate: 2005-12-23 04:38:26
Subject: Re: [BUGS] BUG #2120: Crash when doing UTF8<->ISO_8859_8 encoding

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group