how to modify already defined composite key in liquibase

Question

I need to alter a table to modify the order of the indexes created from the composite key for the below mentioned changeset.

<changeSet author="demo (generated)" id="demo-11">
        <createTable tableName="customersalesdata">
            <column name="id" type="BIGINT">
                <constraints unique="true" primaryKey="true" primaryKeyName="customersalesdata_pkey"/>
            </column>
            <column name="customerid" type="NVARCHAR(255)">
                <constraints primaryKey="true" unique="true" primaryKeyName="customersalesdata_pkey"/>
            </column>   </createTable>
    </changeSet>

The reason for altering is that ordering of the columns in an index makes a big difference. Since the customerid is the second column, it will not be used. The query is performing an index scan because of this. Since the table has two indexes that start with id, having the id, customerid in this order is a waste (in most cases).

So I need to change the column order to customerid and id. And the another problem is customerid which is the composite key, is referred as the foreign key in the another table.

My question is should I need to drop FK first and then drop Composite Keys and then form the composite key in the order as shown below

<changeSet id="2">
        <addPrimaryKey columnNames="customerid, id"
                       constraintName="customersalesdata_pkey"
                       tableName="customersalesdata"
                       validate="true"/>
    </changeSet>

Or just create a another index on top of composite key by combining both of the fields as shown below

<changeSet author="demo" id="demo-id">
        <createIndex tableName="customersalesdata" indexName="idxn_customer_id_id">
            <column name="customer_id"/>
            <column name="id"/>
        </createIndex>
    </changeSet>

Also in both the cases will there be any chances of data loss? Can you please suggest the best approach here.

score 0 · Answer 1 · answered Apr 26 '22 at 22:08

A similar question was asked a while back on this post.

I'll paraphrase the two most popular answers from that thread.

#1

You can read the Liquibase Documentation and there is also a similar problem to reference here. In the case of the situation presented in "Adding composite unique constraint in Liquibase" linked above, the solution is
<changeSet author="liquibase-docs" id="addUniqueConstraint->example">
<addUniqueConstraint
        columnNames="product_id, tournament_id"
        constraintName="your_constraint_name"
        tableName="person"
        />
</changeSet>

#2

I am pretty certain that:

#1 You can't do it inside the createTable tag itself, but you can do it within the same changeset as when the table is created. #1 It does create a composite unique constraint on the two columns. One way you can check is to run liquibase with the command to generate the SQL for an update rather than running the update command and checking what it does for your database. On the command line, rather than running liquibase update, you would run liquibase updateSQL.

how to modify already defined composite key in liquibase

1 Answers1