how to recreate an index if it already exists with liquibase

Question

I have a changeset to create an index.

<changeSet author="hilland" id="x-NC-U-y" runOnChange="true">
    <createIndex indexName="NC-U-y" tableName="x" unique="true">
        <column name="y"/>
    </createIndex>      

    <rollback>
        <dropIndex .../>
    </rollback>

    <modifySql>
        <append value=" INCLUDE ( [a],[b]) WITH (DATA_COMPRESSION=page)"/>
    </modifySql>
</changeSet>

The problem is that an older version of the index might exist (it will on some targets (dev server, with an older version of this index; no includes), will not on others (eg a fresh deploy to an empty database).

Is there a better way to resolve this so that the script becomes universal, than to include a prefix which says

<sql>IF select {sys.indexes.stuff} is not null {drop the index}</sql>?

Ideally there would be a precondition to drop-if-exists the existing index so that it can be recreated, but i don't think that can be done.

What is the best way to handle this situation?

an alternate solution i thought of would be to have a separate changest "if index v1 exists, drop it" then have a separate v2 changeset, but then that would make rollback hard.

I realize that the run on change condition for the changeset makes rollback hard, but the only rollback i'm concerned with in this case is to and from an empty database, although a pattern which easily accommodates rollbacks to previous version would be welcome also.

Answer 1

You can execute changesets dependent on a precondition. Your changeset could look like this:

<changeSet>
    <preConditions onFail="MARK_RAN">
        <indexExists indexName="NC-U-y" />
    </preConditions>
    <dropIndex indexName="NC-U-y" />
</changeSet>

See also the Liquibase Documentation.