How to alter "REFERENCES" in PostgreSQL?

Question

How can I alter the reference to a table in PostgreSQL when the table name has been changed?

Say I have:

CREATE TABLE example1 (    id serial NOT NULL PRIMARY KEY,    name varchar(100) );  CREATE TABLE example2 (    id serial NOT NULL PRIMARY KEY,    example1fk integer REFERENCES example1 (id) DEFERRABLE INITIALLY DEFERRED ); 

Later I do:

ALTER TABLE example1 RENAME TO example3;  

How to change the definition of the foreign key constraint?

example1fk integer REFERENCES example1 (id) DEFERRABLE INITIALLY DEFERRED, 

Answer 1

Internal dependencies between tables and / or other objects are never bound to the object name. Internally, every object is stored in a catalog table and the OID (internal primary key) of the object is used for everything else.

Accordingly, a FOREIGN KEY reference is stored in the catalog tables pg_constraint (the constraint itself incl. its name) and pg_depend. Changing table names will not impair functionality at all.

The name of the constraint remains unchanged. You can ignore that, or you may want to rename the constraint so it's not misleading.

However, since you did not specify a constraint name at creation time, the system picked a default, which is example2_example1fk_fkey in your case unless the name was taken. No reference to the referenced table name. But the column name will likely have to change in your example, too. And that is used in the constraint name.

ALTER TABLE example2 RENAME example1fk TO example3fk;  -- rename column 

In Postgres 9.2 or later you can just rename the constraint as well (as dequis commented):

ALTER TABLE example2 RENAME CONSTRAINT example2_example1fk_fkey TO example2_example3fk_fkey; 

In older versions, you have to drop and recreate the constraint to rename it, best in a single statement:

ALTER TABLE example2  -- rename constraint    DROP CONSTRAINT example2_example1fk_fkey  , ADD  CONSTRAINT example2_example3fk_fkey FOREIGN KEY (example3fk)       REFERENCES example3 (id) DEFERRABLE INITIALLY DEFERRED; 

Details in the manual.