136

Is it possible to change the constraint name in Postgres? I have a PK added with:

ALTER TABLE contractor_contractor ADD CONSTRAINT commerce_contractor_pkey PRIMARY KEY(id);

And I want to to have different name for it, to be consistent with the rest of the system. Shall I delete the existing PK constraint and create a new one? Or is there a 'soft' way to manage it?

Thanks!

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110

4 Answers4

228

To rename an existing constraint in PostgreSQL 9.2 or newer, you can use ALTER TABLE:

ALTER TABLE name RENAME CONSTRAINT constraint_name TO new_constraint_name;
Andrei Petrenko
  • 3,922
  • 3
  • 31
  • 53
Arturo Herrero
  • 12,772
  • 11
  • 42
  • 73
  • 1
    @ArturoHerrero could we do renaming on multiple constraints at once, if yes, how? – Erlan May 03 '17 at 04:28
  • 1
    @Erlan you can obtain the list of all constraints via query to `pg_catalog`, iterate over it via `LOOP` and use a dynamic query to rename. – Evgeny Nozdrev Nov 26 '18 at 12:12
103

For the primary key, you should be able to just:

ALTER INDEX commerce_contractor_pkey RENAME TO whatever_new_name

That won't work for other types of constraints though. The best option there is to drop the old one and create a new one. Be sure to do it inside a transaction, so the system isn't live without it during rebuild. (And if you can't do it in a transaction, be sure to create the new one first, before dropping the old one)

David Schmitt
  • 58,259
  • 26
  • 121
  • 165
Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
7

We found that primary keys often lag behind the main table name. This script helped us identify and fix the ones with issues.

select 
    table_name, 
    constraint_name , 
    'ALTER TABLE ' || table_name || ' RENAME CONSTRAINT ' || constraint_name || ' TO ' || left(table_name, 58) || '_pkey;'
from information_schema.table_constraints tc
where constraint_type  = 'PRIMARY KEY' 
and constraint_name <> left(table_name, 58) || '_pkey';

This finds all the tables where the primary key name is no longer the "default" pattern (<tablename>_pkey) and creates a rename script for each.

The 58 character limit above in code above is to account for the maximum size of constraint names (63bytes).

Obviously sense check what is returned prior to running it. Hope that is helpful for others.

Paul Grimshaw
  • 19,894
  • 6
  • 40
  • 59
1

We were getting a 42704 error when trying to run the command in this format:

ALTER TABLE name RENAME CONSTRAINT constraint_name TO new_constraint_name;

It turned out that our existing contraints began with "FK_", i.e. an upper-case prefix.

In this case, we had to enclose our constraint names in double quotes, i.e.:

ALTER TABLE name RENAME CONSTRAINT "FK_constraint_name" TO "FK_new_constraint_name";
Artie Leech
  • 347
  • 2
  • 14