Re: BUG #2122: Inconsistent FK Error Messages

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