On Feb 23, 2017 12:42 PM, "Ivan Voras" <ivoras@gmail.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:
How to detect if a foreign key depends on an index I'm about to drop, so I can skip it
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.
