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; }