CODEWITHSUNDEEP
mysql.netentity-frameworkasp.net-core.net-core
Harsh Sharma
Harsh Sharma

Reputation: 930

.Net Core2 EF MySQL having issue while changing foreign key column to nullable

I am working on an application where I am using .Net Core 2, EF Core and MySQL as database server via Code First approach.

I have 2 tables:

  1. User
  2. Employee

User table is the main table which contains the user information and Employee table is the child table which has a column ID_User as shown below:

 public class User : BaseEntity
    {
        public int ID_User { get; set; }
        public string Name { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }

        public virtual ICollection<Employee> Employees{get;set;}
    }



 public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int ID_User { get; set; }

        public virtual User User { get; set; }
    }

Everything works perfectly when I use the above mapping and I have enough data in both the tables.

Now, I want to make the column ID_User in Employee table as nullable

To implement this change I made following change to my model:

public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int? ID_User { get; set; }

        public virtual User User { get; set; }
    }

and in mapping file:

builder.HasOne(x=>x.User).WithMany(y=>y.Employees).HasForeignKey(z=>z.ID_User).IsRequired(false);

After running the dotnet ef migrations add empuser command it generated the following migration code:

  migrationBuilder.DropForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee");

        migrationBuilder.AlterColumn<int>(
            name: "ID_User",
            table: "Employee",
            nullable: true,
            oldClrType: typeof(int));

        migrationBuilder.AddForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee",
            column: "ID_User",
            principalTable: "User",
            principalColumn: "ID_User",
            onDelete: ReferentialAction.Restrict);

Now when I run dotnet ef database update it is giving me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT FK_Employee_User_ID_User' at line 1

Please help.

Thanks

Upvotes: 7

Views: 2861

Answers (3)

Krishnanunni Jeevan
Krishnanunni Jeevan

Reputation: 1759

The key word CONSTRAINT is not supported for certain version of MYSQL . EF core generates drop constraint for dropping foreign key . I have to change the following:-

migrationBuilder.DropForeignKey(
            name: "FK_XXXXX",
            table: "XXXXXX");

to

migrationBuilder.Sql("ALTER TABLE XXXXXX DROP FOREIGN KEY FK_XXXXX");

Upvotes: 0

soreal
soreal

Reputation: 264

Have you checked foreign key name in database?

I have found bug from migration builder, where creating new table:

In my case I use EF Core 2.1 Mysql DotNet Connector bug

Where migration builder creates wrongly named foreign key name...

To avoid this wrongly named foreign key name:

fix for migration builder

If your foreign key is named wrongly, you can manually rename it in database and then your migration might work.

Upvotes: 0

Jochem
Jochem

Reputation: 51

Try putting the SQL statements directly into the MySQL Workbench.

  1. Type "dotnet ef migrations script" in your commandprompt.
  2. Copy the generated SQL script.
  3. Paste it into your Workbench.
  4. Check where the errors occur.

When I got similar errors using EF core 2 with MySQL this helped me understand the problem better and helped solve the problem. (for me it was a typing error). You can at least use this method to determine if it is an error in the migrations or in your SQL statements.

I know this is not a concrete solution, but I hope this will help you understand your problem and solve it :)

Upvotes: 5

Related Questions