Hello Claudio Valderrama C.,

am Samstag, 23. März 2002 um 11:07 schrieben Sie:

>> ALTER TABLE ARTIKEL DROP CONSTRAINT FK_ARTIKEL_U_MWST;
>> commit;
>> ALTER TABLE ARTIKEL DROP CONSTRAINT FK_ARTIKEL_V_MWST;
>> commit;
>>
>> The error "index is in use" comes at the second alter table

CVC> It means that the engine has taken the index that surrounds the FK
CVC> referenced and is holding it. By using normal DDL, each DDL command goes in
CVC> its own transaction. The PK has an automatically created index that is to be
CVC> referenced internally by the FKs.

CVC> You drop a constraint and the FK1's index is taken. But it's marked for
CVC> deletion. However, this causes the master to be reloaded because we are
CVC> deleting a trigger in the master: it's the master's triggers that produce
CVC> the CASCADE effect under the hood. Each FK with cascade causes the creation
CVC> of an after delete trigger in the master table. When the second trigger is
CVC> reloaded while the master is adjusted, it causes the detail's second FK's
CVC> index to be locked and it remains locked. This is the same effect than
CVC> trying to get rid of a resource used by a procedure that's prepared. You
CVC> drop FK2, therefore FK2's index should be dropped, but the recompilation of
CVC> the trigger that complements this FK2 holds FK2's index taken and can't be
CVC> taken exclusively again. Hence, INDEX IN USE.

CVC> By doing both commands inside the same transaction, the ill-effect is
CVC> avoided.
CVC> The simple solution is:

CVC> set auto off;
CVC> ALTER TABLE ARTIKEL DROP CONSTRAINT FK_ARTIKEL_U_MWST;
CVC> ALTER TABLE ARTIKEL DROP CONSTRAINT FK_ARTIKEL_V_MWST;
CVC> commit;
CVC> set auto on;

CVC> This way both statements happen inside the same txn, hence no conflict.

thank You for your solution, it helped me finishing the script.
Unfortunately I understood not perfectly why the index remains locked
even if the transaction is committed. Is this a bug or necessary?
I think I understand the mechanism of trigger an
commonly used PK master index, but from my imagination it would be
more a problem to do it in one transaction that in two. But I do not
know enough about the internal principles of IB/FB.

Thank You very much
Dieter Tremel