Click here to Skip to main content
16,015,635 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Introducing FOREIGN KEY constraint 'FK_books_subCategories_SubCategoryId' on table 'books' 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. See previous errors.


What I have tried:

migrationBuilder.CreateTable(
name: "subCategories",
columns: table => new
{
Id = table.Column(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newid())"),
Name = table.Column(type: "nvarchar(20)", maxLength: 20, nullable: false),
CategoryId = table.Column(type: "uniqueidentifier", nullable: false),
CurrentStaut = table.Column(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_subCategories", x => x.Id);
table.ForeignKey(
name: "FK_subCategories_categories_CategoryId",
column: x => x.CategoryId,
principalTable: "categories",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateTable(
name: "books",
columns: table => new
{
Id = table.Column(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newid())"),
Name = table.Column(type: "nvarchar(20)", maxLength: 20, nullable: false),
Author = table.Column(type: "nvarchar(20)", maxLength: 20, nullable: false),
ImageName = table.Column(type: "nvarchar(max)", nullable: false),
FileName = table.Column(type: "nvarchar(max)", nullable: false),
Description = table.Column(type: "nvarchar(max)", nullable: false),
Publish = table.Column(type: "bit", nullable: false),
CategoryId = table.Column(type: "uniqueidentifier", nullable: false),
SubCategoryId = table.Column(type: "uniqueidentifier", nullable: false),
CurrentStaut = table.Column(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_books", x => x.Id);
table.ForeignKey(
name: "FK_books_categories_CategoryId",
column: x => x.CategoryId,
principalTable: "categories",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_books_subCategories_SubCategoryId",
column: x => x.SubCategoryId,
principalTable: "subCategories",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
Posted
Updated 20-Sep-22 3:00am

1 solution

As the error says, you have introduced multiple cascade-delete paths between categories and books: when you delete a category, you have to delete all books in that category, but you also have to delete all sub-categories in that category, which requires deleting all books in those sub-categories.

SQL Server does not allow multiple cascade-delete paths between two tables. You need to change your model so that there is only a single cascade-delete path. For example, change the relationship between books and categories to specify "no action" for the delete behaviour.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900