Re: FK Constraint on index not PK

=?UTF-8?B?U3TDqXBoYW5lIFNjaGlsZGtuZWNodA==?= <stephane(dot)schildknecht(at)postgresqlfr(dot)org> writes:
> My goal is to migrate to 8.2.1. definitely. But as you said it, I do not
> want to recreate unwanted index when migrating. I want to drop them BEFORE.
> But, I can't just do a "drop index" command. It fails.

Right, because the FK constraints by chance seized on those indexes as
being the matching ones for them to depend on.

What you want to do is (1) update the relevant pg_depend entries to
reference the desired PK indexes instead of the undesired ones; then
(2) drop the undesired indexes.

I don't have a script to do (1) but it should be relatively
straightforward: in the rows with objid = OID of FK constraint
and refobjid = OID of unwanted index, update refobjid to be the
OID of the wanted index. (To be truly correct, make sure that
classid and refclassid are the right values; but the odds of a
false match are probably pretty low.)

Needless to say, test and debug your process for this in a scratch
database ... and when you do it on the production DB, start with
BEGIN so you can roll back if you realize you blew it.

regards, tom lane