How to resolve duplicate constraint names in Postgres?

All we need is an easy explanation of the problem, so here it is.

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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply