How to alter "REFERENCES" in PostgreSQL?

postgresql foreign-keys ddl alter-table
Jose Luis de la Rosa picture Jose Luis de la Rosa · Jul 14, 2015 · Viewed 24k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Jul 15, 2015

Internal dependencies between tables and / or other objects are never bound to the object name. Internally, every object is stored in catalog tables 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 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.

Related questions

Delete rows with foreign key in PostgreSQL

I would like to delete rows which contain a foreign key, but when I try something like this: DELETE FROM osoby WHERE id_osoby='1' I get this statement: ERROR: update or delete on table "osoby" violates foreign key …

Read More
Postgres and Indexes on Foreign Keys and Primary Keys

Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?

Read More
Adding a column as a foreign key gives ERROR column referenced in foreign key constraint does not exist

I have the following set up, CREATE TABLE auth_user ( id int PRIMARY KEY ); CREATE TABLE links_chatpicmessage (); I'm trying to add a column named sender to links_chatpicmessage which is a foreign key to another table called auth_user's …

Read More