Cannot update database because entity cannot drop constraint

Question

I created some new tables and I noticed I didn't use the good naming convention for the ids.

So I did a new add-migration with the new ids but when I try to update-database I have the following error :

The constraint 'PK_dbo.DB_User_Type' is being referenced by table 'AspNetUsers', foreign key constraint 'FK_dbo.AspNetUsers_dbo.DB_User_Type_DB_User_Type_Id'. Could not drop constraint. See previous errors.

I don't understand because the script starts by dropping all constraints.

Could someone explain how to solve this error please?

I'm using the new approach code first from an existing database from entity 6.1. Here is the Up function

public override void Up()
        {
            DropForeignKey("dbo.DB_Company_Profile", "DB_Category_Id", "dbo.DB_Category");
            DropForeignKey("dbo.DB_Category_Translation", "DB_Category_Id", "dbo.DB_Category");
            DropForeignKey("dbo.DB_User_Type_Translation", "DB_User_Type_Id", "dbo.DB_User_Type");
            DropForeignKey("dbo.DB_Company_Profile", "category_id", "dbo.DB_Category");
            DropForeignKey("dbo.DB_Category_Translation", "category_id", "dbo.DB_Category");
            DropForeignKey("dbo.AspNetUsers", "DB_User_Type_user_type_id", "dbo.DB_User_Type");
            DropForeignKey("dbo.DB_User_Type_Translation", "user_type_id", "dbo.DB_User_Type");
            DropIndex("dbo.DB_Company_Profile", new[] { "DB_Category_Id" });
            DropIndex("dbo.DB_Category_Translation", new[] { "DB_Category_Id" });
            DropIndex("dbo.DB_User_Type_Translation", new[] { "DB_User_Type_Id" });
            DropColumn("dbo.DB_Company_Profile", "category_id");
            DropColumn("dbo.DB_Category_Translation", "category_id");
            DropColumn("dbo.DB_User_Type_Translation", "user_type_id");
            RenameColumn(table: "dbo.AspNetUsers", name: "DB_User_Type_Id", newName: "DB_User_Type_user_type_id");
            RenameColumn(table: "dbo.DB_Company_Profile", name: "DB_Category_Id", newName: "category_id");
            RenameColumn(table: "dbo.DB_Category_Translation", name: "DB_Category_Id", newName: "category_id");
            RenameColumn(table: "dbo.DB_User_Type_Translation", name: "DB_User_Type_Id", newName: "user_type_id");
            RenameIndex(table: "dbo.AspNetUsers", name: "IX_DB_User_Type_Id", newName: "IX_DB_User_Type_user_type_id");
            DropPrimaryKey("dbo.DB_Category");
            DropPrimaryKey("dbo.DB_User_Type");
            AddColumn("dbo.DB_Category", "category_id", c => c.Int(nullable: false, identity: true));
            AddColumn("dbo.DB_User_Type", "user_type_id", c => c.Int(nullable: false, identity: true));
            AlterColumn("dbo.DB_Company_Profile", "category_id", c => c.Int(nullable: false));
            AlterColumn("dbo.DB_Category_Translation", "category_id", c => c.Int(nullable: false));
            AlterColumn("dbo.DB_User_Type_Translation", "user_type_id", c => c.Int(nullable: false));
            AddPrimaryKey("dbo.DB_Category", "category_id");
            AddPrimaryKey("dbo.DB_User_Type", "user_type_id");
            CreateIndex("dbo.DB_Company_Profile", "category_id");
            CreateIndex("dbo.DB_Category_Translation", "category_id");
            CreateIndex("dbo.DB_User_Type_Translation", "user_type_id");
            AddForeignKey("dbo.DB_Company_Profile", "category_id", "dbo.DB_Category", "category_id", cascadeDelete: true);
            AddForeignKey("dbo.DB_Category_Translation", "category_id", "dbo.DB_Category", "category_id", cascadeDelete: true);
            AddForeignKey("dbo.DB_User_Type_Translation", "user_type_id", "dbo.DB_User_Type", "user_type_id", cascadeDelete: true);
            AddForeignKey("dbo.AspNetUsers", "DB_User_Type_user_type_id", "dbo.DB_User_Type", "user_type_id");
            DropColumn("dbo.DB_Category", "Id");
            DropColumn("dbo.DB_User_Type", "Id");
        }

Then it says it doesn't exist. I guess I have to do something else than update-database — Marc
– Marc, Commented May 6, 2014 at 20:58
I guess I could target another migration but I would like to know if there's something less complicated — Marc
– Marc, Commented May 6, 2014 at 21:00
@DarthVader do you know if there's another way than modifying the database and make a new model? — Marc
– Marc, Commented May 7, 2014 at 15:25

Drewness · Accepted Answer · 2018-03-20 21:53:50Z

3

I had the same problem. This post helped me.

The problem is that EF didn't automatically renamed my FK_CONSTRAINST. As a result the DropForeignKey() wasn't correct. You can solve this by manual SQL queries (check post above). But I solved it by first changing the FK_CONSTRAINST manually in the DB. Remember you can use -Verbose to check the queries. It's easier debugging.

edited Mar 20, 2018 at 21:53

Drewness

5,0804 gold badges35 silver badges51 bronze badges

answered Feb 5, 2016 at 12:04

ErazerBrecht

1,6332 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drewness · Accepted Answer · 2018-03-20 21:55:40Z

2

I had to delete the foreign key manually before running the update-database command. Because the constraint name in my DB is something like: FK_dbo.AspNetUsers_dbo.DB_User_Type_DB_User_TypeId

While the constraint the script is trying to drop is: FK_dbo.AspNetUsers_dbo.DB_User_Type_DB_User_Type_Id

edited Mar 20, 2018 at 21:55

Drewness

5,0804 gold badges35 silver badges51 bronze badges

answered Feb 16, 2017 at 7:38

Chinh Phan

1,50919 silver badges22 bronze badges

Comments

Andrew · Accepted Answer · 2014-05-06 21:02:46Z

1

Well, there are some rules about foreign keys - some of them are meant to handle situations, when you try to delete a record that has another records associated with it. In your case, it seems like you want to apply the CASCADE rule to your relationships. This way entity will delete all its FKs

answered May 6, 2014 at 21:02

Andrew

7,82813 gold badges68 silver badges120 bronze badges

3 Comments

Marc Over a year ago

Is it normal that I have this error then if there's no data?

Marc Over a year ago

Could you provide an example with entity?

Andrew Over a year ago

In order to do the CASCADE, you need to modify the table that establish PK-FK relationships. You need to modify the constraint itself.

Drewness · Accepted Answer · 2018-03-20 21:56:22Z

-1

DropForeignKey("dependantTable", "dependantColumn", "principalTable")
DropPrimaryKey("principalTable")
AddPrimaryKey("principalTable", "principalColumn",)
AddForeignKey("dependantTable", "dependantColumn", "principalTable")

edited Mar 20, 2018 at 21:56

Drewness

5,0804 gold badges35 silver badges51 bronze badges

answered Jul 13, 2015 at 2:50

Gustavo Godinez

1

Collectives™ on Stack Overflow

Cannot update database because entity cannot drop constraint

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related