CODEWITHSUNDEEP
c#entity-framework
Marc
Marc

Reputation: 16512

Cannot update database because entity cannot drop constraint

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

Upvotes: 3

Views: 5080

Answers (4)

Gustavo Godinez
Gustavo Godinez

Reputation: 1

  1. DropForeignKey("dependantTable", "dependantColumn", "principalTable")
  2. DropPrimaryKey("principalTable")
  3. AddPrimaryKey("principalTable", "principalColumn",)
  4. AddForeignKey("dependantTable", "dependantColumn", "principalTable")

Upvotes: -1

Chinh Phan
Chinh Phan

Reputation: 1509

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

Upvotes: 2

ErazerBrecht
ErazerBrecht

Reputation: 1613

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.

Upvotes: 3

Andrew
Andrew

Reputation: 7768

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

Upvotes: 1

Related Questions