Getting invalid foreign key constraint error when running database update

Jake Ford 1 Reputation point
2022-11-27T06:08:31.54+00:00

264502-2022-11-26-23-08-20-fordware-microsoft-visual-stud.png

I understand what the error means, and I can edit the migration for the specific foreign keys to set Delete Action to NoAction... However, I shouldn't be getting this error. As you can see in the discussion below, Angela was able to create my exact project, run the migration and database update, and have no errors at all.

Angela was using Visual Studio Code and MySQL instead, so that is why she didn't get the same thing. I am using Visual Studio 2022, MSSQL Server Management Studio, and I've never had this issue before. I'm just putting this out here in hopes that another dev somewhere out there has seen this.

264511-wwwudemycom-course-complete-aspnet-core-21-course.png

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
721 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,358 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,076 Reputation points Microsoft Vendor
    2022-11-28T02:02:05.65+00:00

    Hi @Jake Ford ,

    As you discussed, the issue relates the cascade delete behaviors.

    Some databases, most notably SQL Server, have limitations on the cascade behaviors that form cycles. When you update/delete the related entities, it might case the following exception:

    Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint ' {Foreign Key}' on table '{Foreign table}' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    There are two ways to handle this situation:

    1. Change one or more of the relationships to not cascade delete.
    2. Configure the database without one or more of these cascade deletes, then ensure all dependent entities are loaded so that EF Core can perform the cascading behavior.

    So, you can set the foreign key as nullable or configure cascading behaviors (such as set it as NoAction). More detailed information, see Database cascade limitations.


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Dillion

    0 comments No comments