... may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

Question

Entity Framework Core

Throwing error while doing update-database

Error:- Introducing FOREIGN KEY constraint 'FK_UserRoleRelationship_UserRoels_ParentUserRoleId' on table 'UserRoleRelationship' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index.

public class UserRoleRelationship 
{
    [Key]
    public int Id { get; set; }
    [Required]
    public Guid UserRoleRelationshipId { get; set; }

    public virtual UserRole ChildUserRole { get; set; }
    public int ChildUserRoleId { get; set; }

    public virtual UserRole ParentUserRole { get; set; }
    public int ParentUserRoleId { get; set; }

}

public class UserRole 
{
    [Key]
    public int Id { get; set; }
    [Required]
    public Guid UserRoleId { get; set; }
    public virtual Role Role { set; get; }
    public int RoleId { set; get; }
    public virtual U.User User { set; get; }
    public int UserId { set; get; }
}

Solution 1

For your current model design, it will create migration below:

            migrationBuilder.AddForeignKey(
            name: "FK_UserRoleRelationship_UserRole_ChildUserRoleId",
            table: "UserRoleRelationship",
            column: "ChildUserRoleId",
            principalTable: "UserRole",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

        migrationBuilder.AddForeignKey(
            name: "FK_UserRoleRelationship_UserRole_ParentUserRoleId",
            table: "UserRoleRelationship",
            column: "ParentUserRoleId",
            principalTable: "UserRole",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

FK_UserRoleRelationship_UserRole_ChildUserRoleId and FK_UserRoleRelationship_UserRole_ParentUserRoleId both will delete the records in UserRole when deleting UserRoleRelationship which will cause multiple cascade delete.

For a workaround, try to make int as int? like below:

        public int? ParentUserRoleId { get; set; }

Which will create

migrationBuilder.AddForeignKey(
                name: "FK_UserRoleRelationship_UserRole_ParentUserRoleId",
                table: "UserRoleRelationship",
                column: "ParentUserRoleId",
                principalTable: "UserRole",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

Note
You need to delete UserRole first, then delete UserRoleRelationship

Solution 2

For a workaround, try to make int as int? like below:

public int? ParentUserRoleId { get; set; }