Description: ------------ MDB2 does not make a difference between a UNIQUE index and a UNIQUE constraint, but PostgreSQL does. A UNIQUE constraint will implicitly create a UNIQUE index, but creating an index manually with "CREATE UNIQUE INDEX" does not make it a constraint. That's no problem as long as we only operate on data, but the manager/reverse functions that operate on constraints do not work with the latter. The corresponding index functions don't either because these indexes should be handled by the constraint functions... The attached patch extends the constraint functions to operate also on UNIQUE indexes that are not part of a real constraint. Test script: --------------- CREATE TABLE foo ( bar INTEGER UNIQUE, baz INTEGER ); CREATE UNIQUE INDEX foo_baz_idx ON foo (baz); This will create 2 indexes: foo_bar_key (implicitly created by the constraint) and foo_baz_idx. Expected result: ---------------- - manager->listTableConstraints() should return both indexes. - manager->dropConstraint() should be able to drop both indexes - reverse->getTableConstraintDefinition() should return identical results for both indexes (except for the different column names) Actual result: -------------- - manager->listTableConstraints() lists only foo_bar_key. - manager->dropConstraint() and reverse->getTableConstraintDefinition() fail on foo_baz_idx because the catalog does not contain a constraint with that name.

@Holger: thanks a lot, I've applied a fix in part different from your patch, but hopefully equivalent. Please check the CVS version and reopen this report if I missed something.

I had a closer look at the CVS fix. It works as expected with my test scripts, with one exception: The extension of dropConstraint() that looks for unique indexes tries the given index name with and without getIndexName() applied to it. Real constraints are handed down to the original implementation which does not do this check but instead only tries the transformed name. This is actually an issue with the MDB2_Driver_Common class. Is this behaviour intended? (This could be avoided by setting idxname_format to '%s', but this could have sideeffects if the application relies on the default value of '%s_idx'. It's also arguable whether a destructive operation like dropConstraint() should *guess* the name.)