MS-SQL unique constraint should allow null

[duderoot]

Context:

• I have two tables Document and Request which are in a one2one relation
• Document is the owning entity i.e. I have a column request_id which is a FK to the table Request
• on the request_id I define a nullable unique constrain e.g.
  <column name="request_id" type="bigint"> <constraints unique="true" nullable="true" uniqueConstraintName="ux_data_sheet_request_id" /> </column>

Problem:

• when creating a unique constrain on a column it should allow null e.g.
• unfortunately the created SQL DDL is not taking in consideration the null values for a MS-SQL DB

Solution:

• for a MS-SQL DB the generated SQL should look like:
  CREATE UNIQUE INDEX ux_document_request_id ON document(request_id) WHERE request_id IS NOT NULL;

┆Issue is synchronized with this Jira Bug by Unito

[is-simon]

@duderoot Did you found a workaround to this ?

[fabiang]

@is-simon workaround is to use <sql> and create the index with an CREATE INDEX statement.

[is-simon]

@fabiang Thank a lot! It worked like a charm!

[filipelautert]

@tati-qalified could you check if this still an issue on core?

[tati-qalified]

@filipelautert this seems to be fixed by Liquibase v4.27.0

Here's my changelog:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

  <changeSet id="2" author="nvoxland">
    <createTable tableName="country">
      <column name="id" type="varchar(50)">
        <constraints primaryKey="true" />
      </column>
      <column name="name" type="varchar(50)" />
    </createTable>
  </changeSet>

  <changeSet id="1" author="nvoxland">
    <createTable tableName="person">
      <column name="id" type="int" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
      </column>
      <column name="firstname" type="varchar(50)" />
      <column name="lastname" type="varchar(50)">
        <constraints nullable="false" />
      </column>
      <column name="country_id" type="varchar(50)">
        <constraints foreignKeyName="fk_country_person" unique="true" referencedTableName="country"
          referencedColumnNames="id" nullable="true" />
      </column>

    </createTable>
  </changeSet>

</databaseChangeLog>

And the relevant part of the generated SQL script after running liquibase update-sql:

CREATE TABLE person (id int IDENTITY (1, 1) NOT NULL, firstname varchar(50), lastname varchar(50) NOT NULL, country_id varchar(50), CONSTRAINT PK_PERSON PRIMARY KEY (id), CONSTRAINT fk_country_person FOREIGN KEY (country_id) REFERENCES country(id), UNIQUE (country_id));
GO

The foreign key column isn't marked as not null:

I'll be closing this ticket as completed.
If anyone comes across this bug again, feel free to leave a comment and we'll reopen it.

Thank you,
Tatiana

[fabiang]

I guess we have a misunderstanding here. SQLServer treats NULL values as non-unique by default on indexes, that why we need WHERE colum_name NOT NULL when creating an index.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

  <changeSet id="1" author="fabiang">
    <createTable tableName="country">
      <column name="id" type="varchar(50)">
        <constraints primaryKey="true" />
      </column>
      <column name="name" type="varchar(50)">
        <constraints nullable="true"/>
      </column>
    </createTable>

    <createIndex tableName="country" indexName="UNIQ_NAME" unique="true">
        <column name="name">
            <constraints nullable="true"/>
        </column>
    </createIndex>
  </changeSet>

  <changeSet id="2" author="fabiang">
    <createTable tableName="country2">
      <column name="id" type="varchar(50)">
        <constraints primaryKey="true" />
      </column>
      <column name="name" type="varchar(50)">
        <constraints nullable="true"/>
      </column>
    </createTable>
  </changeSet>

 <!-- Expected behavior -->
  <changeSet id="3" author="fabiang">
    <createIndex tableName="country2" indexName="UNIQ_NAME" unique="true">
        <column name="name">
            <constraints nullable="true"/>
        </column>
    </createIndex>
    <modifySql>
        <append value=" WHERE name IS NOT NULL" />
    </modifySql>
  </changeSet>
</databaseChangeLog>

Current:

CREATE TABLE country (
  id varchar(50) NOT NULL,
 name varchar(50), 
 CONSTRAINT PK_COUNTRY PRIMARY KEY (id)
);
GO

CREATE UNIQUE NONCLUSTERED INDEX UNIQ_NAME ON country(name);
GO

Expected with workaround:

CREATE TABLE country2 (
  id varchar(50) NOT NULL,
  name varchar(50), 
  CONSTRAINT PK_COUNTRY2 PRIMARY KEY (id)
);
GO

CREATE UNIQUE NONCLUSTERED INDEX 
  UNIQ_NAME ON country2(name) 
  WHERE name IS NOT NULL;
GO

The second version allow multiple NULL values for name.

[tati-qalified]

@fabiang I understand what you mean. However, that's not currently the expected behaviour for Liquibase, so this isn't a bug.
If you would find this functionality helpful, you can submit a feature request and/or a PR to our core repository.

Let me know if you have any other questions.

Thank you,
Tatiana

[is-simon]

@tati-qalified I disagree with you and think this is a bug. We had our database changelogs working fine for years until a client asked us to install our application on an SQL server database. What @fabiang is trying to explain here is that we have to do something different in our changelogs to support a unique index on a nullable column if we plan to use ms-sql, since the generated command for the unique index (on ms-sql) is missing the WHERE xxx IS NOT NULL part, which is required for MS-SQL only...

Here's what we did to fix this. We first drop the faulty contraint created by liquibase. We then create the unique index again properly for ms-sql only:

<createTable tableName="sec_user">
  <!-- other columns -->
  <column name="email" type="varchar(100)">
      <constraints unique="true" uniqueConstraintName="user_uk_02"/>
  </column>
</createTable>

<!--
See https://github.com/liquibase/liquibase-hibernate/issues/201#issue-448128424
We must have a sql block to create the email unique index on mssql.
-->
<sql dbms="mssql">
    alter table sec_user drop constraint user_uk_02;
    create unique index user_uk_02 on sec_user(email) where email is not null;
</sql>

So, I think this is a bug. The unique index is not properly created by liquibase for an MS-SQL database when the column is nullable.

The original issue was about unique index constraint, not foreign key constraint. I don't see a unique index constraint in the changelog you posted to prove that the issue is fixed.

Also, you have to insert 2 rows with a null value for the column with a unique index for the bug to manifest.

[tati-qalified]

@is-simon I've created a ticket on the main Liquibase repository, as this isn't a liquibase-hibernate issue. We'll look into it, but I'd recommend submitting a PR to ensure that the fix is implemented. Our development team is available to provide guidance if needed.

We can continue discussing this issue in the ticket I've created.

Thank you,
Tatiana