foreignKeyConstraintExists Precondition Seems Not Adequate If Foreign Keys with Same Reference Details but Different Names Exist

Question

When I upgrade an existing database using a Liquibase script (the database has never been upgraded by Liquibase), the following error occurs.

liquibase.exception.MigrationFailedException: Migration failed for change set ../master.xml::5::ray.chen:
     Reason: liquibase.exception.DatabaseException: ORA-02275: such a referential constraint already exists in the table
 [Failed SQL: ALTER TABLE CRDM_RCHEN.SOME_TABLE ADD CONSTRAINT SOME_TABLE_FK FOREIGN KEY (COLUMN_2) REFERENCES CRDM_RCHEN.SOME_OTHER_TABLE (COLUMN_1)]

        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:605)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
        at liquibase.Liquibase.update(Liquibase.java:214)
        at liquibase.Liquibase.update(Liquibase.java:192)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1126)
        at liquibase.integration.commandline.Main.run(Main.java:184)
        at liquibase.integration.commandline.Main.main(Main.java:103)

I double checked the Liquibase script and the database, a foreign key named SOME_TABLE_1_FK already exists in the database. The root cause should be that the Liquibase script is going to add a foreign key named SOME_TABLE_FK but a foreign key named SOME_TABLE_1_FK already exists with same reference details and different name. It seems the database does not allow 2 foreign keys which names are different but reference details are same.

Do you have any solutions or ideas about this issue? Thanks.

Here is the related Liquibase script. I use the foreignKeyConstraintExists precondition to check whether the foreign key SOME_TABLE_FK exists, if not, add it; but if there is a foreign key existing with same reference details but different name (the above case), the changeset will be executed and the above error occurs.

<changeSet author="ray.chen" id="5">
    <preConditions onFail="MARK_RAN">
        <not>
            <foreignKeyConstraintExists foreignKeyName="SOME_TABLE_FK" />
        </not>
    </preConditions>
    <addForeignKeyConstraint constraintName="SOME_TABLE_FK" baseTableName="SOME_TABLE" baseColumnNames="COLUMN_2" referencedTableName="SOME_OTHER_TABLE" referencedColumnNames="COLUMN_1" deferrable="false" initiallyDeferred="false" onDelete="RESTRICT" onUpdate="RESTRICT" />
</changeSet>

Answer 1

The foreignKeyConstraintExists provided by liquibase requires foreignKeyName to be passed. in order to check if there is a foreign key without name then you need to write custom precondition. an example for the same can be seen below:

<preConditions onFail="MARK_RAN">
    <not>
        <customPrecondition className="com.ctp.liquibase.ForeignKeyExistsPrecondition">
            <param name="schemaName" value="MYSCHEMA"/>
            <param name="tableName" value="TABLE"/>
            <param name="columnName" value="COLUMN"/>
            <param name="foreignTableName" value="FTABLE"/>
        </customPrecondition>
    </not>
</preConditions>

The above will check if a column on a table has a foreign key constraint for a foreign table.

more you can find here.