Re: Bad REFERENCES behaviour

On Thu, 25 Jan 2001, Christopher Kings-Lynne wrote:

> There seems to be a bug in the 'REFERENCES' statement. You can create
> foreign key references to fields that do not exist, that then cause odd (ie.
> hard to resolve) error messages.
>
> The operator error below (that should not be possible) is in creating a
> reference to a column that does not exist users(id).
>
> My example:
>
> test=# select version();
> version
> -----------------------------------------------------------------
> PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.2, compiled by cc
> (1 row)
>
> test=# create table users(userid int4);
> CREATE
> test=# create table newsletter(user_id int4 references users(id));
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> test=# insert into newsletter values (4);
> ERROR: constraint <unnamed>: table users does not have an attribute id
> test=#
>
> When we got this error message we spent an hour trying to figure out what
> the heck the problem was! In the end we simply deleted the bad trigger by
> oid and just recreated it using CREATE CONSTRAINT TRIGGER.
>
> I have not yet checked whether table foreign key constraints, or the CREATE
> CONSTRAINT TRIGGER functionality has the same bug.

They all did. In 7.1 you should be safe from invalid column names in the
actual constraint definitions but create constraint trigger doesn't check
(because it has no real way of knowing what its parameters are supposed to
mean).