Synopsis:
If you create a table with a foreign key reference, and later change the name
of the referenced field in the referenced table, subsequent updates on the
given table will result in an error that the old field name does not exist.
I've also seen the corollary to this, where an update to the field name in
the base table also causes the error.
Error Message:
ERROR: constraint <unnamed>: table _tablename) does not have an attribute
_old-field-name_
Expected behavior:
Alter table should also update the foreign key functions and triggers as
necessary, such that changing the name of any field will work. At the very
least, changing the name of a foreign key constraint an error on the alter
table statement, something along the lines of "you cannot change the name of
a column which is a foreign key reference".
Workaround:
If you have to change the name of a foreign key field, or the field it
references, you have to drop the given table, recreate it, and reload it with
data.
I suppose there is a way to rewrite the functions and triggers too, but I
haven't toyed with that.
Reproduce:
This example demonstrates changing the name of a field on both ends of a
foreign key constraint.
CREATE TABLE car_make (
type text primary key);
CREATE TABLE car (
vin text primary key,
make text references car_make(type));
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car VALUES('yadayada','Toyota');
ALTER TABLE car_make RENAME type TO make;
INSERT INTO car VALUES('anothercarvin','Toyota');
/* the above errors */
INSERT INTO car_make VALUES('Satrun');
ALTER TABLE car RENAME make TO type;
UPDATE car_make SET make='Saturn' WHERE make='Satrun';
/* the above errors */
Results:
INSERT INTO car_make VALUES('Toyota');
INSERT INTO car_make VALUES('Satrun');
ALTER TABLE car RENAME make TO type;
UPDATE car_make SET make='Saturn' WHERE make='Satrun';
/* the above errors */
INSERT 365791 1
junk=# INSERT INTO car VALUES('yadayada','Toyota');
INSERT 365792 1
junk=# ALTER TABLE car_make RENAME type TO make;
ALTER
junk=# INSERT INTO car VALUES('anothercarvin','Toyota');
ERROR: constraint <unnamed>: table car_make does not have an attribute type
junk=# /* the above errors */
junk-# INSERT INTO car_make VALUES('Satrun');
INSERT 365794 1
junk=#
junk=# ALTER TABLE car RENAME make TO type;
ALTER
junk=# UPDATE car_make SET make='Saturn' WHERE make='Satrun';
ERROR: constraint <unnamed>: table car does not have an attribute make
junk=# /* the above errors */
