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
?
This is on PostgreSQL 9.3.