HSQL: Creating index if not existing

Question

I'm initializing a HSQL database 2.2.9 via Spring using

<jdbc:initialize-database enabled="true">
    <jdbc:script execution="INIT" location="classpath:./create-tables.sql"/>
</jdbc:initialize-database>

In create-tables.sql I use

CREATE TABLE IF NOT EXISTS MyTable(...);

The table also has an index. I'm looking for a better way than always dropping and creating the index.

I tried:

CREATE INDEX IF NOT EXISTS myIndex ...;

• does not work

I can create a function indexExisting() checking the system tables and returning count(*) > 0 if the index is found, but if I write

IF indexExisting() = 0 THEN ...

directly into the .sql file, it says

java.sql.SQLSyntaxErrorException: unexcepted token: IF

Also a stored procedure does not seem to help, as they may not contain DROP statements, as far as I read.

So a solution other than dropping / creating the index would be appreciated.

Thank you

Answer 1

The latest version of HSQLDB supports both:

CREATE INDEX IF NOT EXISTS myIndex ...
DROP INDEX IF EXISTS myIndex