Postgresql – Rename the constraint of a possibly referenced primary key in PostgreSQL

postgresqlprimary-key

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:

SELECT
    tc.table_name, tc.constraint_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    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';

Best Answer

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.

Related Question