""dietertremel"" <tremel@...> wrote in message
news:a7a78c+2o80@......

> Hello,
> Using FB1 I have a script to reorganize my DB with DDL-Statements
> like this:
> 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. What
> does it mean? Thank You
> Dieter Tremel

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

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

Cascade RI is twofold: detail's FK points to master PK or UNIQUE's index. In
turn, master gets a trigger to delete records from detail. In doing so, that
trigger does a search that causes detail's index (the FK index) to be held
locked when that trigger is compiled. This causes the chicken and egg
problem and forces a disconnection in most cases.

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

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

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

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing