MSSQL needs a "nullable" statement for indexes on nullable columns

[tati-qalified]

Search first

• I searched and no similar issues were found

Description

Based on this issue: for MSSQL (and possibly PostgreSQL), if a unique constraint/index is created on a nullable column, WHERE colum_name NOT NULL should be appended to the end of the statement.

Steps To Reproduce

Changelog:

  <changeSet id="1" author="liquibase">
    <createTable tableName="country">
      <column name="name" type="varchar(50)"></column>
    </createTable>
  </changeSet>


  <changeSet id="2" author="liquibase">
    <createIndex tableName="country" indexName="countryName" unique="true">
      <column name="name" />
    </createIndex>
  </changeSet>

  <changeSet id="3" author="liquibase">
    <insert tableName="country">
      <column name="name" value="null"></column>
    </insert>
    <insert tableName="country2">
      <column name="name" value="null"></column>
    </insert>
  </changeSet>

Generated SQL for the index:

 CREATE UNIQUE NONCLUSTERED INDEX countryName ON country (name);

When running liquibase update:

Unexpected error running Liquibase: Migration failed for changeset changelog.xml::3::liquibase:
     Reason: liquibase.exception.DatabaseException: Cannot insert duplicate key row in object 'dbo.country' with unique index 'countryName'. The duplicate key value is (<NULL>). [Failed SQL: (2601) INSERT INTO country (name) VALUES (NULL);]

Expected/Desired Behavior

The generated SQL statement should be CREATE UNIQUE NONCLUSTERED INDEX countryName ON country (name) WHERE name IS NOT NULL;

Liquibase Version

4.27.0 and prior

Database Vendor & Version

MSSQL

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[filipelautert]

So it's an index (unique or not), and if the column is added with constraint nullable then you append the where is not null for it.
We would need to account for more than one column in the index when generating the where, and check which databases support it - MSSQL and Postgresql are the ones I know from the top of my head.