Re: FK Constraint on index not PK

Read the release notes. 7.4.8 and 7.4.11 require special considerations. By all means upgrade, but it's not quite seamless.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Scott Marlowe
Sent: Friday, January 12, 2007 12:59 PM
To: Stéphane Schildknecht
Cc: pgsql general
Subject: Re: [GENERAL] FK Constraint on index not PK

On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote:
> Dear community members,
>
> I'm having a quite strange behaviour while trying to drop some index.
>
> We have some tables with two indexes on a primary key. The first one was
> automatically created by the primary constraint. The second one was
> manually created on the same column. Don't know why, but I would now
> want to suppress it.
>
> The first index is : foo_pkey
> The second one : i_foo_pk
> The constraint on table bar is fk_bar_foo references foo(id)
>
> But, when trying to drop the second index I get the following message :
>
> NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk
>
> The database server is 7.4.5 .
>
> Having dumped database and restored it on a 8.2 server, I could drop the
> second index without any problem.
>
> The fact is I could do that as I indded what to migrate all databases
> from 7.4 to 8.2. But I would prefer not to recreate every index before
> dropping the non necessary one. And duplicate indexes are surely
> unnecessary...
>
> I have read in some thread that these troubles are known and have been
> corrected in versions > 7.4.5. But, droping them before migrating is an
> option I'd prefer to use.

Simple. Backup your db just in case, then upgrade in place to the
latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in
place.

patch upgrades do NOT require dump / reload, so 7.4.5 -> 7.4.15 should
only require a brief period where you shut down the db while you install
the new version.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings