Unable to delete FK from model code-first

Question

I have a created an MVC application using the code-first approach with an existing database.

I have noticed that one of my tables doesn't need a foreign key, so I have tried to delete it but I keep getting:

The object 'FK_BorrowedProperty_codeStatus' is dependent on column 'StatusId'. ALTER TABLE DROP COLUMN StatusId failed because one or more objects access this column.

Steps I have done

• Gone into BorrowedProperty class and deleted public int StatusId { get; set; } & public virtual codeStatu codeStatu { get; set; }
• Went into CodeStatu class and deleted [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<BorrowedProperty> BorrowedProperties { get; set; }
• Gone into my DbContext and commented out this line //modelBuilder.Entity<codeStatu>() // .HasMany(e => e.BorrowedProperties) // .WithRequired(e => e.codeStatu) // .HasForeignKey(e => e.StatusId) // .WillCascadeOnDelete(false);
• Add-Migration DeleteStatusFKFromBorrowedProperty

That results in:

public partial class DeleteStatusFKFromBorrowedProperty : DbMigration
{
    public override void Up()
    {
        DropForeignKey("dbo.BorrowedProperty", "StatusId", "dbo.codeStatus");
        DropIndex("dbo.BorrowedProperty", new[] { "StatusId" });
        DropColumn("dbo.BorrowedProperty", "StatusId");
    }

    public override void Down()
    {
        AddColumn("dbo.BorrowedProperty", "StatusId", c => c.Int(nullable: false));
        CreateIndex("dbo.BorrowedProperty", "StatusId");
        AddForeignKey("dbo.BorrowedProperty", "StatusId", "dbo.codeStatus", "Id");
    }
}

• update-database

Then I receive the error above. There are zero records in the BorrowedProperty database table.

What am I doing wrong?

Answer 1

From exception message it is clear that you've foreign key constraint named FK_BorrowedProperty_codeStatus which prevents StatusId column to be dropped. You can create a method which contains SQL command to remove FK constraint like this:

private void DropForeignKeyConstraint()
{
    // This command may work if the constraint name is known:
    // Sql(@"ALTER TABLE [dbo].[BorrowedProperty] DROP CONSTRAINT [FK_BorrowedProperty_codeStatus]");

    Sql(@"DECLARE @constraint NVARCHAR(128)
        SELECT @constraint = name
        FROM sys.default_constraints
        WHERE parent_object_id = object_id(N'dbo.BorrowedProperty')
        AND col_name(parent_object_id, parent_column_id) = 'StatusId';
        IF @constraint IS NOT NULL
           EXECUTE('ALTER TABLE [dbo].[BorrowedProperty] DROP CONSTRAINT [' + @constraint + ']')");
}

Put the method inside Up method on migration code like this:

public partial class DeleteStatusFKFromBorrowedProperty : DbMigration
{
    public override void Up()
    {
        DropForeignKeyConstraint(); // dropping FK constraint first
        DropForeignKey("dbo.BorrowedProperty", "StatusId", "dbo.codeStatus");
        DropIndex("dbo.BorrowedProperty", new[] { "StatusId" });
        DropColumn("dbo.BorrowedProperty", "StatusId"); // drop column at last
    }
}

Or if you've access to SSMS instance, run this query directly:

ALTER TABLE [dbo].[BorrowedProperty] DROP CONSTRAINT [FK_BorrowedProperty_codeStatus]

NB: You can try commenting out all code lines in Down method to see if foreign key constraint doesn't recreated.

Afterwards, you can perform Add-Migration & Update-Database in Package Manager Console to apply changes above.

Similar issues:

The object 'DF__*' is dependent on column '*' - Changing int to double

How do I drop a column with object dependencies in SQL Server 2008?

Entity Framework Migrations can't drop table because of foreign key constraint