Thread: Odd Foreign Key Bug

I'm using PostgreSQL 7.3.4 on cygwin

A coding error on my part revealed what appears
to be a a bug in PostgreSQL's foreign key behavior.

reduced bug-revealing code:
******************************
create table languages(
    language_code char(2) not null,
    language varchar(100) not null,
    constraint languages_pk primary key (language_code)
);

create table content (
 content_id integer not null,
 language_code integer not null,
 body text,
 constraint content_pk primary key (content_id)
);

alter table content
 add constraint languages_content_fk foreign key (language_code) references
languages on delete restrict on update cascade;
 ****************
Now, after these definitions run:

insert into languages values ('AA', 'whocares');

Then do:

update languages set language_code = lower(language_code);

And you get the error:

ERROR:  column "language_code" is of type integer but expression is of type
character
        You will need to rewrite or cast the expression

PostgreSQL seems to think that language_code is an integer
because of an errant foreign key constraint.

Note that language_code integer in the content table references
language_code char(2) in the languages table (This was a mistake by me that
revealed this odd behavior).

Also notice that the foreign key constraint is not declared until after the
table
definition.  This error does not show up if the foreign key is added when
the content
table is defined.

I don't know if this is a bug or expected behavior for non-sensical SQL
code, but,
at the very least, PostgreSQL displays different behavior depending on when
you
declare the foreign key constraint, which seems strange.

Regards,
Ara Anjargolian

On Mon, 6 Oct 2003, Ara Anjargolian wrote:
> Also notice that the foreign key constraint is not declared until after
> the table definition.  This error does not show up if the foreign key is
> added when the content table is defined.

It works the same for me as a table constraint or as an alter as long as
on update cascade is given in both cases.  It'd be nice to fail at
constraint creation time if on update cascade is given and the type on the
primary key table wasn't assignment convertable to the type on the foreign
key table.