FluentMigrator - Check if Foreign Key exists before deleting it

Question

I am using FluentMigrator to migrate one database schema to another. I have a case in which I want to check if a foreign key exists before deleting it.

Previously, I just delete the foreign key by doing:

Delete.ForeignKey("FK_TableName_FieldName").OnTable("TableName");

How do I check that the foreign key exists first?

Rudder

Answer 1

This is how to delete a foreign key if it exists using FluentMigrator:

if (Schema.Table("TableName").Constraint("FK_TableName_FieldName").Exists())
{
   Delete.ForeignKey("FK_TableName_FieldName").OnTable("TableName");
}

Gates

Answer 2

Based on this https://mcmap.net/q/126508/-how-can-i-find-out-what-foreign-key-constraint-references-a-table-in-sql-server you can use Execute.WithConnection function to test if foreign key exist before delete it.

    Execute.WithConnection((connection, transaction) =>
    {
        DeleteForeignKeyIfExist(connection, transaction, "yourReferencedTable", "yourTable", "foreignColumnName", "foreignKeyName");
    });

    public bool DeleteForeignKeyIfExist(IDbConnection connection, IDbTransaction transaction, string referenceTable, string table, string foreignKeyColumn, string foreignKeyConstrainName)
    {
        using (var cmd = transaction.Connection.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = ForeignKeyExistCommand(referenceTable, foreignKeyColumn);

            bool foreignKeyExist = false;
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // If this code is reached, the foreign key exist
                    foreignKeyExist = true;
                    break;
                }
            }

            if (foreignKeyExist)
            {
                cmd.CommandText = $"ALTER TABLE [{table}] DROP CONSTRAINT [{foreignKeyConstrainName}];";

                cmd.ExecuteNonQuery();
                return true;
            }
        }

        return false;
    }

    private string ForeignKeyExistCommand(string foreignTable, string innerColumn)
    {
        return $"SELECT OBJECT_NAME(f.parent_object_id) TableName, " +
                "COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName " +
                "FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc " +
                "ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t " +
               $"ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME(f.referenced_object_id) = '{foreignTable}' " +
               $"and COL_NAME(fc.parent_object_id,fc.parent_column_id) = '{innerColumn}'";
    }

Profiteer