Liquibase Preconditions of Unique Constraints (indexExists) Doesn't Work for HSQLDB Databases?
<p>When I upgrade an existing HSQLDB database (<em>the database does not contain the 2 Liquibase control tables - <strong>databasechangelog</strong> and <strong>databasechangeloglock</em></strong>) using a Liquibase script (<em>displayed as follows</em>), an error occurs reporting <code>java.sql.SQLSyntaxErrorException: object name already exists: SOME_TABLE_UK in statement [ALTER TABLE PUBLIC.SOME_TABLE ADD CONSTRAINT SOME_TABLE_UK UNIQUE (COLUMN_3, COLUMN_4)]</code>.</p>

<pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd"&gt;
    &lt;property name="TYPE_AS_ID" value="VARCHAR2(36 BYTE)" dbms="oracle" /&gt;
    &lt;property name="TYPE_AS_ID" value="VARCHAR(36)" dbms="postgresql" /&gt;
    &lt;property name="TYPE_AS_ID" value="VARCHAR(36)" dbms="hsqldb" /&gt;

    &lt;property name="TYPE_AS_NORMAL_TEXT" value="VARCHAR2(255 BYTE)" dbms="oracle" /&gt;
    &lt;property name="TYPE_AS_NORMAL_TEXT" value="TEXT" dbms="postgresql" /&gt;
    &lt;property name="TYPE_AS_NORMAL_TEXT" value="VARCHAR(255)" dbms="hsqldb" /&gt;

    &lt;changeSet author="RayChen" id="1"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;tableExists tableName="SOME_TABLE" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;createTable tableName="SOME_TABLE"&gt;
            &lt;column name="COLUMN_1" type="${TYPE_AS_ID}"&gt;
                &lt;constraints nullable="false" /&gt;
            &lt;/column&gt;
            &lt;column name="COLUMN_2" type="${TYPE_AS_ID}" /&gt;
            &lt;column name="COLUMN_3" type="${TYPE_AS_NORMAL_TEXT}" /&gt;
            &lt;column name="COLUMN_4" type="${TYPE_AS_NORMAL_TEXT}" /&gt;
        &lt;/createTable&gt;
    &lt;/changeSet&gt;

    &lt;changeSet author="RayChen" id="2"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;tableExists tableName="SOME_OTHER_TABLE" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;createTable tableName="SOME_OTHER_TABLE"&gt;
            &lt;column name="COLUMN_1" type="${TYPE_AS_ID}"&gt;
                &lt;constraints nullable="false" /&gt;
            &lt;/column&gt;
            &lt;column name="COLUMN_2" type="${TYPE_AS_NORMAL_TEXT}" /&gt;
        &lt;/createTable&gt;
    &lt;/changeSet&gt;

    &lt;changeSet author="RayChen" id="3"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;primaryKeyExists tableName="SOME_TABLE" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;addPrimaryKey constraintName="SOME_TABLE_PK" tableName="SOME_TABLE" columnNames="COLUMN_1" /&gt;
    &lt;/changeSet&gt;

    &lt;changeSet author="RayChen" id="4"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;primaryKeyExists tableName="SOME_OTHER_TABLE" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;addPrimaryKey constraintName="SOME_OTHER_TABLE_PK" tableName="SOME_OTHER_TABLE" columnNames="COLUMN_1" /&gt;
    &lt;/changeSet&gt;

    &lt;changeSet author="RayChen" id="5"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;foreignKeyConstraintExists foreignKeyName="SOME_TABLE_FK" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;addForeignKeyConstraint constraintName="SOME_TABLE_FK" baseTableName="SOME_TABLE" baseColumnNames="COLUMN_2" referencedTableName="SOME_OTHER_TABLE" referencedColumnNames="COLUMN_1" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" /&gt;
    &lt;/changeSet&gt;

    &lt;changeSet author="RayChen" id="6"&gt;
        &lt;preConditions onFail="MARK_RAN"&gt;
            &lt;not&gt;
                &lt;indexExists indexName="SOME_TABLE_UK" /&gt;
            &lt;/not&gt;
        &lt;/preConditions&gt;
        &lt;addUniqueConstraint constraintName="SOME_TABLE_UK" tableName="SOME_TABLE" columnNames="COLUMN_3, COLUMN_4" /&gt;
    &lt;/changeSet&gt;
&lt;/databaseChangeLog&gt;
</code></pre>

<p>But I have already added a <strong>not indexExists</strong> precondition in the changeset to create that <strong>SOME_TABLE_UK</strong> unique key as follows, why does the above error occur?</p>

<pre><code>&lt;changeSet author="RayChen" id="6"&gt;
    &lt;preConditions onFail="MARK_RAN"&gt;
        &lt;not&gt;
            &lt;indexExists indexName="SOME_TABLE_UK" /&gt;
        &lt;/not&gt;
    &lt;/preConditions&gt;
    &lt;addUniqueConstraint constraintName="SOME_TABLE_UK" tableName="SOME_TABLE" columnNames="COLUMN_3, COLUMN_4" /&gt;
&lt;/changeSet&gt;
</code></pre>

<p>What makes me more confused is, the Liquibase script works well without errors when the database is Oracle or PostgreSQL (<em>the above change set #6 will be skipped due to the <strong>not indexExists</strong> precondition</em>), and similar code works well without errors for primary keys and foreign keys.</p>