--- In firebird-support@yahoogroups.com, "k_smith_000"
<k_smith_000@y...> wrote:

>
> Hello,
> At page 263 of Operation Guide I've found script to generate
> statements that rebuild indices:
>
> SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;'
> FROM RDB$INDICES
> WHERE RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL;
>
> I've tried to create stored procedure
>
> CREATE PROCEDURE SERVICE_INDEX_REBUILD
> AS
> DECLARE VARIABLE S VARCHAR(200);
> DECLARE VARIABLE S2 VARCHAR(200);
> BEGIN
>
> FOR select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
> OR RDB$SYSTEM_FLAG IS NULL INTO :S DO
> BEGIN
> S = 'ALTER INDEX ' || s || 'inactive;';
> EXECUTE STATEMENT :S;
>
> S2 = 'ALTER INDEX ' || s || 'active;';
> EXECUTE STATEMENT :S2;
> END
> SUSPEND;
> END
>
> Unfortunatelly when I try to execute procedure I got following error:
>
> EXECUTE PROCEDURE SERVICE_INDEX_REBUILD;
> Statement failed, SQLCODE = -607
>
> unsuccessful metadata update
> -MODIFY RDB$INDICESS failed
> -action cancelled by trigger (2) to preserve data integrity
> -Cannot deactivate index used by an Integrity Constraint
>
>
> Best regards,
> Kevin Smith
>

The OPGuide was written for Interbase, most of it is still good, but
there are some items that have changed. Primary Keys, Foreign Keys,
Unique constraints etc are internally implemented using an indices.

There used to be a bug that allowed you to deactivate a primary key
index which allowed you to do nasty things like store duplicates in
the primary key field.

Now run your query in isql

select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
OR RDB$SYSTEM_FLAG IS NULL

All the primary key fields are there. You will run into trouble if you
are allowed to deactivate those ones.

Also, lose the suspend. You are not returning any data, so why would
you want to return a row?

Adam