How to create conditional indexes using createIndex?

General Discussion
1

Greetings and thanks for the great work on this project.

I’m coming back to Liquibase after a long period of absence. I’ve downloaded version 3 and started working to create Liquibase change logs from an existing database.

One of my tables has a nullable column on which I create a unique index.
I used the generateChangeLog command to generate the change logs for my database. The resulting refactoring that the command generated for this index is:


  1.      

Which when run against a database generates the following SQL:

  1. CREATE UNIQUE INDEX IX_USER_PERSON_ID ON [dbo].USER

Which is not the same as the original index definition generated by SQL Server Management Studio:

  1. CREATE UNIQUE NONCLUSTERED INDEX [IX_USER_PERSON_ID] ON [dbo].[USER]
    (
        [PERSON_ID] ASC
    )
    WHERE ([PERSON_ID] IS NOT NULL)

The latter is a conditional index, while the one generated by Liquibase prevents inserting multiple NULL values.

Is there a way to create such a conditional index using createIndex without resorting to using the SQL refactoring?


Thanks and regards,

2

The built-in createIndex doesn’t support it, but you could use Nathan

3

The modifySql docs is at http://www.liquibase.org/documentation/modify_sql.html


It probably would be better to have the append in a text block, but it’s trying to be consistent with the other modifySql tags.


I’ll talk to the zoho people about the notification buttons, thanks for pointing it out.


Nathan

4

Thank you for pointing me to this as the modifySql change is not documented.
When I applied it, the XML validator raised error flags as the append tag can’t contain text, and the SQL must be placed in the required value attribute.
I think that it will better to place the SQL inside the append tag (like in your answer) instead of the value attribute, because SQL text tends to be long and separated into multiple lines, and to be consistent with the other refactorings like the sql refactoring.

There’s an issue not related to my question: The notification emails the forum sends have buttons instead of links, which email clients disable

5

Thanks. I searched the list of the available refactorings, but didn’t notice that it’s in a different topic.

6

Hi Guys I am having similar issue and both append and computed="true" is not helping me.

DB: SQL Server

Liquibase version: 3.6.3

I have this following index in my SQL Server Database

    CREATE NONCLUSTERED INDEX XCR_ACTIVE ON dbo.CS_PA_VEH_BASE ( ETL_ACTIVE_FL ) WHERE ETL_ACTIVE_FL = 'N' ON "default";

for which `liquibase` gives out following:

 

                          

But when I want to execute it (also in MS SQL Server) I get the following

    > **Error:**  (14.2) 05-24-19 11:43:05 (E) (13004:15088) RUN-050304: |Session JOB_ODS_Liquibase Function call update error: 1: Unexpected error running Liquibase: Incorrect syntax     > near '('. [Failed SQL: CREATE NONCLUSTERED INDEX XCR_ACTIVE ON     > [dbo].[CS_PA_VEH_DELTA](([ETL_ACTIVE_FL]='N'))] ) > failed, due to     > error <50316>: Liquibase: Incorrect syntax near '('. [Failed SQL: CREATE NONCLUSTERED     > INDEX XCR_ACTIVE ON [dbo].[CS_PA_VEH_DELTA](([ETL_ACTIVE_FL]='N'))]>.

Can you please help?

7