Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key?

ALTER INDEX foo_pkey RENAME TO pk__foo__id;

I have just tried it and it seems OK, but I have heard it is necessary to drop foreign keys, rename pkey (drop/recreate) and re-reference the table again.

In case it is not that easy, is there a way how to find all referencing tables, drop their fkeys, alter the index and atach them again in one SQL?

Here is what I am using to find tables and their fkeys referencing my table:

    tc.table_name, tc.constraint_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name ='foo';
  • 1
    Using ALTER INDEX ... RENAME TO ... should be perfectly safe. Where did you hear otherwise? – Josh Kupershmidt Feb 11 '15 at 14:21
  • @JoshKupershmidt Other databases, such as this one I found in SE Teradata require to do a drop/recreate and I couldn't find anything about it in PostgreSQL. – Qwerty Feb 11 '15 at 14:24

Not sure why Teradata has that limitation, but ALTER INDEX ... RENAME TO ... should be fine in PostgreSQL even when other tables have foreign keys depending on that index. PostgreSQL has fairly sophisticated tracking of such dependencies -- for example, if you tried to do ALTER TABLE foo DROP CONSTRAINT foo_pkey; you would see a complaint like:

ERROR: cannot drop constraint foo_pkey on table foo because other objects depend on it

DETAIL: constraint bar_fkey on table bar depends on index foo_pkey

HINT: Use DROP ... CASCADE to drop the dependent objects too.

You can also see in the pg_constraint table (e.g. via SELECT * FROM pg_constraint WHERE conname = 'bar_fkey' for some referencing foreign key bar_fkey) that PostgreSQL is internally tracking this dependency by the OID (in particular, the conindid column in pg_constraint) of the index rather than the name of the index. The OID of the index does not change when you use ALTER INDEX ... RENAME TO ..., which is how Postgres avoids becoming confused by the rename.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.