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.
So I wonder if ther is a way to indicate my foreign key it has to use
the right primarry key constraint and not an arbitrary index on that
primary key.
(Almost 10 databases and >300 tables to migrate with something like 130
indexes badly created). So I'd alse prefer not to drop every fk
constraint before dropping index and recreating constraint...
Thanks by advance
Stéphane Schildknecht