On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to resolve this name conflict through ALTER TABLE RENAME CONSTRAINT, but that yields an error:

table x has multiple constraints named "y"

Which sounds like in order to rename the constraint I first need to… rename the constraint. 🤔

So, how can I do that?

(Side note: the release notes for PostgreSQL 11 have "Fully enforce uniqueness of table and domain constraint names", alluding to this very situation, I suppose, as I discovered while preparing for an upgrade.)

Minimal steps to reproduce:

create table t1 (id uuid primary key);
create table t2 (id uuid primary key);
alter table t1 add constraint oops foreign key (id) references t2 (id);
alter table t1 add constraint oops unique (id);
alter table t1 rename constraint oops to oopsie;
  • 1
    I cannot create a table with the same name for a unique and foreign key constraint in 9.6. How did you manage to do that? Commented May 19, 2021 at 8:04
  • 1
    You might have to update conname in pg_constraint. But be very careful that you update only one row. And take a backup before you perform surgery on the catalog. Commented May 19, 2021 at 8:06
  • Drop index (according constraint should be dropped without dup error) then recreate it specifying the constraint name. Recommendation - specify all constraints names always.
    – Akina
    Commented May 19, 2021 at 8:09

1 Answer 1


My solution was to drop and recreate the constraints, with one of them given a different name. Postgres will let me drop one constraint by name, but I don't know how it chooses which one or whether it's even deterministic, so the safe route from scripting was to drop both and recreate from there. In the above case, that would go like:

alter table t1 
    drop constraint oops, 
    drop constraint oops,
    add constraint oops foreign key (id) references t2 (id),
    add constraint oops_unique unique (id);

Fortunately, my table was small enough for this to be a practical solution. If it weren't, I'd try updating pg_constraint directly, as Laurenz suggested.

  • Can you edit the answer and add the commands you used to drop both constraints? Commented May 20, 2021 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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