CODEWITHSUNDEEP
hsqldbliquibase
Ray Chen
Ray Chen

Reputation: 182

Liquibase Preconditions of Unique Constraints (indexExists) Doesn't Work for HSQLDB Databases?

When I upgrade an existing HSQLDB database (the database does not contain the 2 Liquibase control tables - databasechangelog and databasechangeloglock) using a Liquibase script (displayed as follows), an error occurs reporting 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)].

<?xml version="1.0" encoding="UTF-8"?>
<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">
    <property name="TYPE_AS_ID" value="VARCHAR2(36 BYTE)" dbms="oracle" />
    <property name="TYPE_AS_ID" value="VARCHAR(36)" dbms="postgresql" />
    <property name="TYPE_AS_ID" value="VARCHAR(36)" dbms="hsqldb" />

    <property name="TYPE_AS_NORMAL_TEXT" value="VARCHAR2(255 BYTE)" dbms="oracle" />
    <property name="TYPE_AS_NORMAL_TEXT" value="TEXT" dbms="postgresql" />
    <property name="TYPE_AS_NORMAL_TEXT" value="VARCHAR(255)" dbms="hsqldb" />

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

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

    <changeSet author="RayChen" id="3">
        <preConditions onFail="MARK_RAN">
            <not>
                <primaryKeyExists tableName="SOME_TABLE" />
            </not>
        </preConditions>
        <addPrimaryKey constraintName="SOME_TABLE_PK" tableName="SOME_TABLE" columnNames="COLUMN_1" />
    </changeSet>

    <changeSet author="RayChen" id="4">
        <preConditions onFail="MARK_RAN">
            <not>
                <primaryKeyExists tableName="SOME_OTHER_TABLE" />
            </not>
        </preConditions>
        <addPrimaryKey constraintName="SOME_OTHER_TABLE_PK" tableName="SOME_OTHER_TABLE" columnNames="COLUMN_1" />
    </changeSet>

    <changeSet author="RayChen" id="5">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists foreignKeyName="SOME_TABLE_FK" />
            </not>
        </preConditions>
        <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" />
    </changeSet>

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

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

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

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

Upvotes: 1

Views: 13654

Answers (3)

Mohammed Nosirat
Mohammed Nosirat

Reputation: 372

I have faced this issue with mysql and liquibase version 4.18.0 using yaml

my script was

 - changeSet:
     id: unique_constraint_id
     author: User (generated)
     preConditions:
       - onFail: MARK_RAN
       - not:
           uniqueConstraintExists:
             tableName: x_table
             constraintName: unique_constraint_on_x_table
     changes:
         - addUniqueConstraint:
            columnNames: id, lang
            constraintName: unique_constraint_on_x_table
            tableName: x_table

I have changed it as @KingDSL said with fixs some names and it worked

- changeSet:
      id: unique_constraint_id
      author: User (generated)
      preConditions:
        - onFail: MARK_RAN
        - sqlCheck:
            expectedResult: 0
            sql: "select count(*) from information_schema.table_constraints where CONSTRAINT_NAME like '%unique_constraint_on_x_table%'"
      changes:
        - addUniqueConstraint:
            columnNames: id, lang
            constraintName: unique_constraint_on_x_table
            tableName: x_table

Upvotes: 2

manish
manish

Reputation: 331

Liquibase starts supporting uniqueconstraint check on precondition since 4.9.0

uniqueConstraintExists  
Checks for the existence of unique constraints before running the update. (since Liquibase 4.9.0)

<preConditions>
<uniqueConstraintExists constraintName="uq_constraint_name" tableName="test_table"/>
</preConditions>

<preConditions>
<uniqueConstraintExists tableName="test_table" columnNames="first_name, last_name"/>
</preConditions>

Upvotes: 2

KingDSL
KingDSL

Reputation: 119

There is another approach to this issue. You can manage your own precontions by using the sql system objects. (I'm not sure if this is the correct HSQLDB syntax) .

<changeSet author="RayChen" id="6">
        <preConditions onFail="MARK_RAN">
             <sqlCheck expectedResult="0">SELECT COUNT(*) FROM sys.objects WHERE name LIKE 'SOME_TABLE_UK%'</sqlCheck>
        </preConditions>
        <addUniqueConstraint constraintName="SOME_TABLE_UK" tableName="SOME_TABLE" columnNames="COLUMN_3, COLUMN_4" />
<changeSet/>

Upvotes: 4

Related Questions