Search code examples
hsqldb

HSQL: Creating index if not existing

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

Solution

  • The latest version of HSQLDB supports both:

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