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

Question

I have reverse-engineered the existing database to the code-first model. Some tables are to be kept but most are to be removed and completely re-architected for the new version.

I delete some old classes and their mapping and add-migration.

The migration looks like this:

  public override void Up()
        {
            DropForeignKey("dbo.Bingo_Review", "BingoID", "dbo.Bingo");
            DropForeignKey("dbo.Bingo_Review_Text", "BingoReviewID", "dbo.Bingo_Review");
            DropForeignKey("dbo.Bingo_Bonus", "BingoID", "dbo.Bingo");
            DropForeignKey("dbo.Bingo_Bonus_Amount", "BingoBonusID", "dbo.Bingo_Bonus");
            DropIndex("dbo.Bingo_Bonus", new[] { "BingoID" });
            DropIndex("dbo.Bingo_Review", new[] { "BingoID" });
            DropIndex("dbo.Bingo_Review_Text", new[] { "BingoReviewID" });
            DropIndex("dbo.Bingo_Bonus_Amount", new[] { "BingoBonusID" });
            DropTable("dbo.Bingo_Bonus");
            DropTable("dbo.Bingo");
            DropTable("dbo.Bingo_Review");
            DropTable("dbo.Bingo_Review_Text");
            DropTable("dbo.Bingo_Bonus_Amount");
            DropTable("dbo.Bingo_Bonus_Type");
        }

However when I run the migration, I get the following error in package manager console.

Could not drop object 'dbo.Bingo_Bonus' because it is referenced by a FOREIGN KEY constraint.

Why do I get this error when the migration should have already dropped any foreign keys prior to the drop table command? Is there any way around this?

May be you still forgot to remove BingoBonusID from any table. Once check it may be u will got solution. — Bhavin, Jul 19 '15 at 11:48
Thanks. However I searched the solution for bingo and the only reference to it is in the migration class I provided above. — Dan Cook, Jul 19 '15 at 12:18

score 44 · Answer 1 · answered Dec 31 '15 at 17:31

44

If the dbo.Bingo_Bonus table name has ever changed, or if any of the columns in the foreign key relationships have changed, EF does not rename the foreign key constraints automatically to match. I had a similar problem and I had to manually add a line like this because the DropForeignKey() function was not actually deleting the key it was supposed to:

Sql(@"ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_dbo.Constraint_Name_From_Before_Table_Change]");

answered Dec 31 '15 at 17:31

Andy Raddatz

2,792
1
27
29

1

Thanks!! Didn't know that EF does not rename foreign key constraints. I solved a similar problem by first changing the FK Constraint manually. – ErazerBrecht Feb 05 '16 at 10:54
14

Same but little bit nicer: DropForeignKey("dbo.MyTable","FK_dbo.Constraint_Name_From_Before_Table_Change"); – akl22 Jun 24 '16 at 12:45
Thank you very much for the explanation, I was beyond confused why my drop foreign key command seemed to have no effect (I renamed it at one point) – Zach Apr 09 '18 at 19:26
@akl22 Thank you sooo much. This really saved me. During Development, I changed the Name of the table. But not anymore. Note to myself: Never change Tablename AGAIN. Thank you guys so much. – Marty_in_a_Box Aug 21 '20 at 09:00

score 7 · Answer 2 · answered Aug 01 '15 at 14:35

You cannot drop Bingo_Bonus table, because it still has references to Bingo_Bonus_Amount and Bingo_Bonus_Type tables, changing the order in the Up() method will solve the problem

by putting :

DropTable("dbo.Bingo_Bonus_Amount");
DropTable("dbo.Bingo_Bonus_Type");

before:

DropTable("dbo.Bingo_Bonus");

Your code will be:

 public override void Up()
    {
        DropForeignKey("dbo.Bingo_Review", "BingoID", "dbo.Bingo");
        DropForeignKey("dbo.Bingo_Review_Text", "BingoReviewID", "dbo.Bingo_Review");
        DropForeignKey("dbo.Bingo_Bonus", "BingoID", "dbo.Bingo");
        DropForeignKey("dbo.Bingo_Bonus_Amount", "BingoBonusID", "dbo.Bingo_Bonus");
        DropIndex("dbo.Bingo_Bonus", new[] { "BingoID" });
        DropIndex("dbo.Bingo_Review", new[] { "BingoID" });
        DropIndex("dbo.Bingo_Review_Text", new[] { "BingoReviewID" });
        DropIndex("dbo.Bingo_Bonus_Amount", new[] { "BingoBonusID" });
        DropTable("dbo.Bingo_Bonus_Amount");
        DropTable("dbo.Bingo_Bonus_Type");
        DropTable("dbo.Bingo_Bonus");
        DropTable("dbo.Bingo");
        DropTable("dbo.Bingo_Review");
        DropTable("dbo.Bingo_Review_Text");

    }

score 3 · Answer 3 · answered Feb 27 '20 at 15:49

Use the DropForeignKey() override that takes the name of the Foreign Key.

DropForeignKey("dbo.BingoReview","dbo.FK_Constraint_Name");

And like the other responses mention, if the table has been changed, you can use the original name.

DropForeignKey("dbo.BingoReview","dbo.FK_Constraint_Name_From_Before_Table_Change");

Thanks @akl22 for providing this in a comment.

score 2 · Answer 4 · answered Dec 24 '16 at 04:10

2

I was able to drop using GUI. When tried to run the query with alter , the '.' symbol was having some error displayed

answered Dec 24 '16 at 04:10

Arun Prasad E S

9,489
8
74
87

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

4 Answers4

Linked