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,
compiledby 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.
Chris