CODEWITHSUNDEEP
liquibaseliquibase-hibernate
Adarsh
Adarsh

Reputation: 33

Liquibase drop foreign key constraints issue

We have the foreign key set up on a table and would now like to delete them. In order to delete them, I have the following changeset

    <changeSet author="me" id="drop-foreign-key-constraints-my_table_1">
    <!-- if either or both foreign key exists, drop the foreign keys-->
        <preConditions onFail="MARK_RAN">
            <or>
                <foreignKeyConstraintExists foreignKeyName="fk_ref_1"/>
                <foreignKeyConstraintExists foreignKeyName="fk_ref_2"/>
            </or>
        </preConditions>
        <dropAllForeignKeyConstraints baseTableName="my_table_1"/>
    </changeSet>

So, the table my_table_1 has two foreign keys and if either exists, I would like to execute the changeset. I do not see any mistake in the definition, however, I see the below liquibase exception and cannot seem to figure out why this error is thrown at all.

liquibase.exception.MigrationFailedException: Migration failed for change set classpath:/create-table-my_table_1.xml::drop-foreign-key-constraints_my_table_1::me:
     Reason: liquibase.exception.UnexpectedLiquibaseException: Expected to return only foreign keys for base table name: my_table_1 and got results for table: my_table_1
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:659)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:97)
    at liquibase.Liquibase.update(Liquibase.java:201)
    at liquibase.Liquibase.update(Liquibase.java:178)
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:368)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:316)

Upvotes: 0

Views: 2617

Answers (1)

Adarsh
Adarsh

Reputation: 33

Answering it for future reference: Liquibase does not consider the table name for the <foreignKeyConstraintExists> check. We wrote a custom Constraint that handles this

private static final String SELECT_QUERY = "SELECT COUNT(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ? AND TABLE_NAME =? AND TABLE_SCHEMA=?";

@Override
public void check(final Database database)
        throws CustomPreconditionFailedException, CustomPreconditionErrorException {

    final JdbcConnection databaseConnection = (JdbcConnection) database.getConnection();
    final String schema = database.getDefaultSchemaName();
    try {

        final int numberOfRows = this.getRowCount(databaseConnection, schema);
        if (numberOfRows == 0) {
            throw new CustomPreconditionFailedException(
                    String.format("Foreign key %s does not exist in the table %s, nothing to delete.",
                            this.tableName, this.foreignKeyName));
        }
    } catch (DatabaseException | SQLException exception) {
        throw new CustomPreconditionErrorException("Unable to check if foreign key exists", exception);
    }
}

private int getRowCount(final JdbcConnection databaseConnection, final String schema)
            throws DatabaseException, SQLException {
        try (PreparedStatement pstmt = databaseConnection.prepareStatement(SELECT_QUERY)) {
            pstmt.setString(1, this.foreignKeyName);
            pstmt.setString(2, this.tableName);
            pstmt.setString(3, schema);
            try (ResultSet resultSet = pstmt.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt(1);
                } else {
                    final String msg = String.format(
                            "Unable to check if foreign key %s exists in table %s, result-set is empty",
                            this.foreignKeyName, this.tableName);
                    log.error(msg);
                    throw new DatabaseException(msg);
                }
            }
        }
    }

Upvotes: 1

Related Questions