internal bool IsForeignKey(IDbConnection connection, out ForeignKeyRef fk)
{
fk = null;
var result = connection.QueryFirstOrDefault(
@"SELECT
[fk].[name] AS [ConstraintName], [t].[name] AS [TableName], SCHEMA_NAME([t].[schema_id]) AS [Schema]
FROM
[sys].[foreign_key_columns] [fkcol] INNER JOIN [sys].[columns] [col] ON
[fkcol].[parent_object_id]=[col].[object_id] AND
[fkcol].[parent_column_id]=[col].[column_id]
INNER JOIN [sys].[foreign_keys] [fk] ON [fkcol].[constraint_object_id]=[fk].[object_id]
INNER JOIN [sys].[tables] [t] ON [fkcol].[parent_object_id]=[t].[object_id]
WHERE
SCHEMA_NAME([t].[schema_id])=@schema AND
[t].[name]=@tableName AND
[col].[name]=@columnName" , new { schema = this.Schema, tableName = this.TableName, columnName = this.ColumnName });
if (result != null)
{
fk = new ForeignKeyRef()
{
ConstraintName = result.ConstraintName, ReferencingTable = new DbObject(result.Schema, result.TableName)
};
return(true);
}
return(false);
}
//private readonly IEnumerable<ForeignKeyRef> _foreignKeys; you won't be dropping the key usually, so there's really no need to drop dependent FKs
internal DropColumn(ColumnRef columnRef, IDbConnection connection) : base(MergeObjectType.Column, MergeActionType.Delete, columnRef.ToString())
{
_columnRef = columnRef;
ForeignKeyRef fk;
if (columnRef.IsForeignKey(connection, out fk))
{
_dropFK = fk;
}
//_foreignKeys = GetReferencingForeignKeys(connection, columnRef.ObjectID);
_cn = connection;
}
private string DropFKStatement(ForeignKeyRef fk)
{
return($"ALTER TABLE [{fk.ReferencingTable.Schema}].[{fk.ReferencingTable.Name}] DROP CONSTRAINT [{fk.ConstraintName}]");
}
public DropForeignKey(ForeignKeyRef fk) : base(MergeObjectType.ForeignKey, MergeActionType.Drop, $"Drop foreign key {fk.ConstraintName}", nameof(DropForeignKey))
{
_fk = fk;
}
