How to drop an existing index in Firebird?

Question

What a script should I create to check is this index exist? Because I want this index will be dropped if it has been created yet and then create the index again

CREATE INDEX IF NOT EXISTS IDX_TABLE ON TABLE (ID, DATE)

I need only one script to make it automatically. I have not found an alternative for with drop_existing = ON as in MSSQL.

You say you want to drop it, but the script you show will conditionally **create** an index, it will not drop it. — Mark Rotteveel, Feb 04 '20 at 17:30

Mark Rotteveel · Accepted Answer · 2021-01-01T11:06:08.127

Unfortunately, Firebird does not allow you to conditionally drop or create an index in its SQL dialect. If you are executing per statement, you could catch errors and ignore the relevant error codes. Alternatively, you could use an execute block, something like:

execute block as
begin
  if (exists(select * from rdb$indices where rdb$index_name = 'IDX_TABLE')) then
    execute statement 'drop index IDX_TABLE';
end

The use of execute statement is necessary, because PSQL (the Firebird procedural language) does not support DDL statements directly.

If instead you want to conditionally create an index, you can use:

execute block as
begin
  if (not exists(select * from rdb$indices where rdb$index_name = 'IDX_TABLE')) then
    execute statement 'create index IDX_TABLE on table (id, name)';
end

The RDB$INDICES table is a Firebird system table.

How to drop an existing index in Firebird?

1 Answers1