HSQL: Creating index if not existing

2018-06-04 20:31:25

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

最新版本的HSQLDB支持：

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

链接地址: http://www.djcxy.com/p/15626.html