# Preconditions are unable to evaluate indexes by their columns, and columnNames attribute appears to be entirely nonfuctional/ignored

**URL:** <https://forum.liquibase.org/t/preconditions-are-unable-to-evaluate-indexes-by-their-columns-and-columnnames-attribute-appears-to-be-entirely-nonfuctional-ignored/5140>\
**Category:** General Discussion\
**Created:** [March 1, 2021, 7:34pm UTC](https://forum.liquibase.org/t/preconditions-are-unable-to-evaluate-indexes-by-their-columns-and-columnnames-attribute-appears-to-be-entirely-nonfuctional-ignored/5140 "2021-03-01T19:34:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![renekabis](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@renekabis](https://forum.liquibase.org/u/renekabis)\
**Post date:** [March 1, 2021, 7:34pm UTC](https://forum.liquibase.org/t/preconditions-are-unable-to-evaluate-indexes-by-their-columns-and-columnnames-attribute-appears-to-be-entirely-nonfuctional-ignored/5140/1 "2021-03-01T19:34:16Z")

</div>

I am running into a situation where preconditions are not behaving properly when they deal with indexes that cover multiple columns.

On the one hand, I have this:

```
<changeSet id="1" author="me" context="test">
    <preConditions onFail="MARK_RAN" onFailMessage="This index does not exist to be removed.">
        <and>
            <indexExists tableName="table_name" indexName="IX_table_name_index"/>
            <not>
                <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
            </not>
        </and>
    </preConditions>
    <dropIndex tableName="table_name" indexName="IX_table_name_index" />
    <rollback/>
</changeSet>

```

Essentially, if an index exists with the correct name, but WITHOUT with the correct contents, it should get dropped/deleted (but if it does exist with _all of the correct_ columns, this changeset should be bypassed and ignored).

This DOES NOT WORK, mainly because it’s not catching correctly-named indexes that are missing one or more of the required columns. For example, if a pre-existing index with the name `IX_table_name_index` contains _only_ columns `column_two` and `column_three`, it is _not_ being dropped as per the very explicit instructions.

On the other hand, I have this:

```
<changeSet id="3" author="me" context="test">
    <preConditions onFail="MARK_RAN" onFailMessage="This index already exists and will not be created.">
        <not>
            <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
        </not>
    </preConditions>
    <createIndex clustered="false" tableName="table_name" indexName="IX_table_name_index">
        <column descending="false" name="column_one"/>
        <column descending="false" name="column_two"/>
        <column descending="false" name="column_three"/>
        <column descending="false" name="column_four"/>
    </createIndex>
</changeSet>

```

Essentially, if an index with the correct name AND _all of the correct columns_ exists, do not try to re-create it.

When run in isolation, this _does work_, but it works _too well_. As in, I am _unable to create an index name collision._ (Why a collision? Well, to prove that this precondition is actually being correctly parsed)

When I have a pre-existing index with the name `IX_table_name_index`, but without all of the required four columns, I am _unable_ to generate an index name collision - the script fails to attempt an index creation, despite the pre-existing index not fulfilling the precondition definition for a `MARK_RAN` flag.

As such, I strongly believe that the `columnNames` attribute is entirely nonfunctional, and is actually being completely ignored - making it more than just useless, but an actual hindrance and a source of issues. Does this represent a flaw within Liquibase that would call for a bug report?

Edit, for clarity: MSSQL 2016/2019 on Windows, and utilizing a Java project. If it matters.

---

<div class="post-metadata">

**Author:** ![EduardUta](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.liquibase.org/eduarduta/32/324_2.png) [@EduardUta](https://forum.liquibase.org/u/EduardUta)\
**Post date:** [March 2, 2021, 8:08pm UTC](https://forum.liquibase.org/t/preconditions-are-unable-to-evaluate-indexes-by-their-columns-and-columnnames-attribute-appears-to-be-entirely-nonfuctional-ignored/5140/2 "2021-03-02T20:08:51Z")

</div>

Hi @renekabis ,

This is a valuable/proper use case I think you have here.  
While this might not be the answer to your question, here are some notes and some past experiences working with indexes, Liquibase and SQL Server.

- First of all, `columnNames` I don’t think is even an index related attribute. `columnName` it is. This might be the reason behind the ignore/precondition failure.
- Liquibase does not support included columns feature that SQL Server offers. Be really careful with this, might impact the precondition / limit your index creation cases.
- Always use in the create index or any index related precondition the tableName as well (paired with the indexName).
- You can always use the sqlCheck precondtion type to cover any case that Liquibase does not cover. This would be a candidate case for using a sqlCheck precondition (and the actual SQL statement would check for the index in sys.indexes (joining sys.objects etc) with all the things you need).

Hope this helps,  
Eduard

---

<div class="post-metadata">

**Author:** ![renekabis](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@renekabis](https://forum.liquibase.org/u/renekabis)\
**Post date:** [March 2, 2021, 9:30pm UTC](https://forum.liquibase.org/t/preconditions-are-unable-to-evaluate-indexes-by-their-columns-and-columnnames-attribute-appears-to-be-entirely-nonfuctional-ignored/5140/3 "2021-03-02T21:30:26Z")

</div>

Unfortunately, the XSD file for Liquibase does not agree with your description on that first point: `columnNames` is accepted as an attribute for the `indexExists` element, but the `columnName` attribute is flagged as an illegal element attribute for `indexExists`.