0

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)
Adarsh
  • 33
  • 8
  • https://cockroachlabs.com/blog/common-foreign-key-mistakes/#2-dangling-foreign-keys - "The best way to avoid dangling foreign keys is to use a modern database system that can validate constraints when they’re added to a table, and that won’t allow users to break the database’s referential integrity by removing data that’s required by another table. If upgrading isn’t possible, though, the next-best defense against dangling foreign keys is good documentation that highlights foreign key relationships, and careful planning, particularly if anything is being removed from the database." – tabbyfoo Jun 01 '22 at 21:04
  • 1
    the actual issue was that the foreign name was duplicated and liquibase actually does not consider the table name while checking if the foreign key exists (even when the table name is supplied). We wrote a custom precondition to handle that on our own. – Adarsh Jun 10 '22 at 11:34

1 Answers1

1

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);
                }
            }
        }
    }
Adarsh
  • 33
  • 8