How do I make a repeatable index script?

Question

I'm trying to build a Firebird script that will ensure that a certain index exists and is created correctly. After a bit of Googling around, I got what seems to be the right syntax:

SET TERM ^ ;
execute block as begin
IF (EXISTS(SELECT RDB$INDEX_NAME
  FROM RDB$INDICES
 WHERE RDB$RELATION_NAME='TABLE_NAME'
 and RDB$INDEX_NAME = 'INDEX_NAME')) THEN
execute statement 'drop index INDEX_NAME';
end
SET TERM ; ^

CREATE UNIQUE INDEX INDEX_NAME
  ON TABLE_NAME
  (FIELD1, FIELD2, FIELD3);

This will run once, and it works fine. But if I try to run it a second time, I get a "the index already exists" error, which indicates that the execute statement part isn't actually running.

What am I missing? How do I make this script work right?

Answer 1

Use WITH AUTONOMOUS TRANSACTION clause. The following code works for me:

EXECUTE BLOCK
AS BEGIN
  IF (EXISTS(SELECT RDB$INDEX_NAME FROM RDB$INDICES
    WHERE RDB$RELATION_NAME = '<relation_name>'
      AND RDB$INDEX_NAME = '<index_name>')) THEN
  BEGIN 
    EXECUTE STATEMENT 'drop index <index_name>'
      with autonomous transaction;
  END

  EXECUTE STATEMENT 'create index <index_name> on ...'
    with autonomous transaction;
END

Answer 2

Have you committed the transaction?

Most relational databases run in a transaction by default. Until you commit or rollback the transaction is pending. You need to

drop index foo

commit -- some implementations use 'commit work' others 'commit transaction'

create index foo on bar ( col_1 , ... , col_n )

commit -- some implementations use 'commit work' others 'commit transaction'