How to alter "REFERENCES" in PostgreSQL? How to alter "REFERENCES" in PostgreSQL? postgresql postgresql

How to alter "REFERENCES" in PostgreSQL?


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.