Re: Foreign key references a unique index instead of a primary key

On Feb 23, 2017 12:42 PM, "Ivan Voras" <ivoras(at)gmail(dot)com> wrote:

Hello,

I've inherited a situation where:

- a table has both a primary key and a unique index on the same field.
- at some time, a foreign key was added which references this table
(actually, I'm not sure about the sequence of events), which has ended up
referencing the unique index instead of the primary key.

Now, when I've tried dropping the unique index, I get an error that the
foreign key references this index (with a hint I use DROP...CASCADE).

This drop index is a part of an automated plpgsql script which deletes
duplicate indexes, so I'm interested in two things:

1. How to detect if a foreign key depends on an index I'm about to drop,
so I can skip it
2. Is there a way to get around this situation, maybe modify the
pg_constraint table or other tables to reference the index / primary key I
want

You could recreate the primary key USING the unique index. This can be done
in a transaction without scanning the table. That way there's only one
index left.

ALTER TABLE my_table
ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;

http://dba.stackexchange.com/questions/8814/how-to-promote-an-existing-index-to-primary-key-in-postgresql