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, May 06 '14 at 20:58
I guess I could target another migration but I would like to know if there's something less complicated — Marc, May 06 '14 at 21:00
@DarthVader do you know if there's another way than modifying the database and make a new model? — Marc, May 07 '14 at 15:25

score 3 · Answer 1 · edited Mar 20 '18 at 21:53

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 '18 at 21:53

Drewness

5,004
4
32
50

answered Feb 05 '16 at 12:04

ErazerBrecht

1,583
2
20
37

score 2 · Answer 2 · edited Mar 20 '18 at 21:55

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 '18 at 21:55

Drewness

5,004
4
32
50

answered Feb 16 '17 at 07:38

Chinh Phan

1,459
19
22

score 1 · Answer 3 · answered May 06 '14 at 21:02

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 06 '14 at 21:02

Andrew

7,619
13
63
117

Is it normal that I have this error then if there's no data? – Marc May 06 '14 at 21:11
Could you provide an example with entity? – Marc May 06 '14 at 21:38
In order to do the CASCADE, you need to modify the table that establish PK-FK relationships. You need to modify the constraint itself. – Andrew May 07 '14 at 15:29

score -1 · Answer 4 · edited Mar 20 '18 at 21:56

-1

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

edited Mar 20 '18 at 21:56

Drewness

5,004
4
32
50

answered Jul 13 '15 at 02:50

Gustavo Godinez

1

Cannot update database because entity cannot drop constraint

4 Answers4

Linked