The following bug has been logged online:

Bug reference: 2122
Logged by: David Wheeler
Email address: david@justatheory.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!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I believe the word "table" was suppressed because that message has an ON
clause that the others do not, and perhaps there was concern that the
word "table" would overflow the display line.

However, I think you have a good point that the text should all use
"table" in both places in the string and I have made that change for
8.2. The new message will be:

ERROR: update or delete on table "a" violates foreign key constraint
"b_a_id_fkey" on table "b"


---------------------------------------------------------------------------

David Wheeler wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2122
> Logged by: David Wheeler
> Email address: david@justatheory.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!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Dec 28, 2005, at 8:44 AM, Bruce Momjian wrote:

> However, I think you have a good point that the text should all use
> "table" in both places in the string and I have made that change for
> 8.2. The new message will be:
>
> ERROR: update or delete on table "a" violates foreign key constraint
> "b_a_id_fkey" on table "b"

Cool, thanks Bruce. I'll update my test scripts accordingly. :-)

Best,

David

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings