Generated changelog has "add" foreign key constraint before "Drop" foreign Key constraint. liquibase jar version : 4.10

Liquibase Development
1

I have to upgrade liquibase-core jar from 3.4.2 to version > 4.8 for my application. In our application we are generating the changelog.xml via CLI command. we are using liquibase with Oracle PL/SQL

I tried with liquibase-core 4.10 version. But the generated diff changelog has wrong order of foreign key constraints.

Output from liquibase-core 3.4.2 version:

These are the only change log which got generated for this table for this version.

<changeSet author="oracle (generated)" id="1679476433218-565">
        <dropForeignKeyConstraint baseTableName="ENDPOINTPROBE_SUBNETS" constraintName="FKENDPNTPRB_SBNTS_SUBNET_ID"/>
        <addForeignKeyConstraint baseColumnNames="SUBNET_ID" baseTableName="ENDPOINTPROBE_SUBNETS" constraintName="FKENDPNTPRB_SBNTS_SUBNET_ID" referencedColumnNames="ID" referencedTableName="ENDPOINTPROBESUBNETS"/>
    </changeSet>

Output from liquibase-core 4.10.0 version:

This version has generated more number of change log. It has generated the change log for all the keys present in table. Point to note is that for all the other constraints drop has been generated first and then add. Only for foreign key it is other way round.

<changeSet author="oracle (generated)" id="1678988969531-465">
        <dropIndex indexName="FIENDPNTPRB_SBNTS_SUBNET_ID" tableName="ENDPOINTPROBE_SUBNETS"/>
    </changeSet>
    <changeSet author="oracle (generated)" id="1678988969531-466">
        <createIndex indexName="FIENDPNTPRB_SBNTS_SUBNET_ID" tableName="ENDPOINTPROBE_SUBNETS" unique="false">
            <column name="SUBNET_ID"/>
        </createIndex>
    </changeSet>
     <changeSet author="oracle (generated)" id="1678988969531-489">
        <dropIndex indexName="FINDPNTPRB_SBNTS_PSNDPNTPRB" tableName="ENDPOINTPROBE_SUBNETS"/>
    </changeSet>
    <changeSet author="oracle (generated)" id="1678988969531-490">
        <createIndex indexName="FINDPNTPRB_SBNTS_PSNDPNTPRB" tableName="ENDPOINTPROBE_SUBNETS" unique="false">
            <column name="ENDPOINTPROBE"/>
        </createIndex>
    </changeSet>
     <changeSet author="oracle (generated)" id="1678988969531-2041">
        <dropPrimaryKey tableName="ENDPOINTPROBE_SUBNETS"/>
    </changeSet>
    <changeSet author="oracle (generated)" id="1678988969531-2042">
        <createIndex indexName="PKENDPOINTPROBE_SUBNETS" tableName="ENDPOINTPROBE_SUBNETS" unique="true">
            <column name="ENDPOINTPROBE"/>
            <column name="SUBNET_ID"/>
        </createIndex>
    </changeSet>
    <changeSet author="oracle (generated)" id="1678988969531-2043">
        <addPrimaryKey columnNames="ENDPOINTPROBE, SUBNET_ID" constraintName="PKENDPOINTPROBE_SUBNETS" forIndexName="PKENDPOINTPROBE_SUBNETS" tableName="ENDPOINTPROBE_SUBNETS"/>
    </changeSet>
    <changeSet author="oracle (generated)" id="1678988969531-3437">
        <addForeignKeyConstraint baseColumnNames="SUBNET_ID" baseTableName="ENDPOINTPROBE_SUBNETS" constraintName="FKENDPNTPRB_SBNTS_SUBNET_ID" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" referencedColumnNames="ID" referencedTableName="ENDPOINTPROBESUBNETS" validate="true"/>
    </changeSet>
     <changeSet author="oracle (generated)" id="1678988969531-3451">
        <dropForeignKeyConstraint baseTableName="ENDPOINTPROBE_SUBNETS" constraintName="FKENDPNTPRB_SBNTS_SUBNET_ID"/>
    </changeSet>

While applying these change logs it errors out saying :

Caused by: Error : 2264, Position : 57, Sql = ALTER TABLE ENDPOINTPROBE_SUBNETS ADD CONSTRAINT FKENDPNTPRB_SBNTS_SUBNET_ID FOREIGN KEY (SUBNET_ID) REFERENCES ENDPOINTPROBESUBNETS (ID), OriginalSql = ALTER TABLE ENDPOINTPROBE_SUBNETS ADD CONSTRAINT FKENDPNTPRB_SBNTS_SUBNET_ID FOREIGN KEY (SUBNET_ID) REFERENCES ENDPOINTPROBESUBNETS (ID), Error Msg = ORA-02264: name already used by an existing constraint

The ddlsql for the table is

prompt create table EndPointProbe_subnets
    create table EndPointProbe_subnets(
        EndPointProbe varchar2(36) CONSTRAINT NN45447695037066403 NOT NULL,
        SUBNET_ID varchar2(36) CONSTRAINT NN45447695037083963 NOT NULL,
        CONSTRAINT PKEndPointProbe_subnets PRIMARY KEY (EndPointProbe, SUBNET_ID)
    );
    ALTER TABLE EndPointProbe_subnets ADD CONSTRAINT FKndPntPrb_sbnts_PSndPntPrb FOREIGN KEY (EndPointProbe) REFERENCES EndPointProbe (ID);
    ALTER TABLE EndPointProbe_subnets ADD CONSTRAINT FKEndPntPrb_sbnts_SUBNET_ID FOREIGN KEY (SUBNET_ID) REFERENCES EndPointProbeSubnets (ID);                              
    CREATE INDEX FIndPntPrb_sbnts_PSndPntPrb ON EndPointProbe_subnets (EndPointProbe);
    CREATE INDEX FIEndPntPrb_sbnts_SUBNET_ID ON EndPointProbe_subnets (SUBNET_ID);

Can someone help me in resolving this issue.

I tried with the different versions of liquibase - 4.0, 4.8 and 4.10. But all are generating the same changelog.xml

2

@priyanka - if you’ll open a GitHub issue with this information, we’ll review it for you.

Here is the link: Open a Liquibase Issue

Thanks!

3

Thanks Kevin
Raised the below issue, please check

Priyanka

4

We’re looking at it. Thanks!