Subject Is it true that old alter index ... inactive scripts are invalid for Firebird 3.0?
Author
Post date 2019-08-08T09:56:19Z
Hi!

Sometimes I need to deactive all indices (for some special processing whose intermediate results can invalidate constraints) and then reactive them all again. Of course, I can do this with DB restore option which does not activate indices during restore process. But so far I have always preferred to generate scripts inactivation and activation scripts for indices using:

SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;' FROM RDB$INDICES WHERE (RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL) and (RDB$FOREIGN_KEY is null);

SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;' FROM RDB$INDICES WHERE (RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL) and (RDB$FOREIGN_KEY is not null);

Now I am trying to use this process for Firebird 3.0, but I am always receiving:

unsuccessful metadata update. XXX failed.
action cancelled by trigger (2) to preserve data integrity.
Cannot deactivate index used by an integrity constraint.


As I rememeber then I was able to reactive foreign keys always in the past for the previouse Firebird versions, is is really true that I can not do this in Firebird 3.0? Or maybe there is some command for inactivating foreign key indices and then inactivating primary key indices which have been references by foreign keys?


Jonatan