Re: FK Constraint on index not PK

Tom Lane a écrit :
> =?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
>
Hi Tom,

Thank You very much for this answer. I'll try that tomorrow morning.

regards,

SAS