I am getting delete cascade error like below:
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint ‘FK_Products_Categories_CategoriesId’ on table ‘Products’ 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.
I have check other solutions of related problem but nothing seems to working here.
this is my Product class
public class Products : ITimeAuditable
{
[Key]
[Required]
public string Id { get; set; }
[Required]
[MaxLength(100)]
public string Name { get; set; }
[Range(0, double.MaxValue)]
public double StockAmount { get; set; }
[Range(0, double.MaxValue)]
public double MinStockAmount { get; set; }
[Range(0, double.MaxValue)]
public double UnitPrice { get; set; }
[Required]
[MaxLength(20)]
public string UnitType { get; set; }
[Required]
public DateTime CreatedAtUtc { get; set; }
public DateTime? UpdatedAtUtc { get; set; }
public string? UserId { get; set; }
[ForeignKey("UserId")]
public Users User { get; set; }
public int? CategoriesId { get; set; }
[ForeignKey("CategoriesId")]
public Categories Categories { get; set; }
public ICollection<Transactions> Transactions { get; set; } = new List<Transactions>();
}
this is my dbcontext
builder.Entity<Products>()
.HasOne(p => p.User)
.WithMany(u => u.Products)
.HasForeignKey(p => p.UserId)
.OnDelete(DeleteBehavior.NoAction);
builder.Entity<Products>()
.HasOne(p => p.Categories)
.WithMany(c => c.Products)
.HasForeignKey(p => p.CategoriesId)
.OnDelete(DeleteBehavior.Restrict);
}
Transactions entity
public class Transactions
{
[Key]
public int Id { get; set; }
[Range(0, double.MaxValue)]
public double Qty { get; set; }
[Required]
[MaxLength(20)]
public string? UnitType { get; set; }
[Range(0, double.MaxValue)]
public double TotalPrice { get; set; }
[Required]
[MaxLength(20)]
public string? TransactionTypes { get; set; }
[Required]
public DateTime DateTime { get; set; }
[Required]
public string? UserId { get; set; }
[ForeignKey("UserId")]
public Users Users { get; set; }
public string? ProductsId { get; set; }
[ForeignKey("ProductsId")]
public Products? Products { get; set; }
}
Users entity
public class Users : IdentityUser, ITimeAuditable
{
[Required]
public DateTime CreatedAtUtc { get; set; }
public DateTime? UpdatedAtUtc { get; set; }
public ICollection<Products> Products { get; set; } = new List<Products>();
public ICollection<Transactions> Transactions { get; set; } = new List<Transactions>();
}
public class Categories:ITimeAuditable
{
[Key]
public int Id { get; set; }
[Required]
public string Name { get; set; }
public DateTime CreatedAtUtc { get; set; }
public DateTime? UpdatedAtUtc { get; set; }
public ICollection<Products> Products { get; set; }
public string UsersId { get; set; }
[ForeignKey("UsersId")]
public Users Users { get; set; }
}
I checked by putting both Restrict but same result.
5
After checking the repo and I am facing the same issue, here is the output. Then we can copy the sql statement and excute it in the database, then we can find more details.
PM> Update-Database -Context AppDbContext
Build started...
Build succeeded.
Applying migration '20240925073600_init'.
Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Products] (
[Id] nvarchar(450) NOT NULL,
[Name] nvarchar(100) NOT NULL,
[StockAmount] float NOT NULL,
[MinStockAmount] float NOT NULL,
[UnitPrice] float NOT NULL,
[UnitType] nvarchar(20) NOT NULL,
[CreatedAtUtc] datetime2 NOT NULL,
[UpdatedAtUtc] datetime2 NULL,
[UserId] nvarchar(450) NOT NULL,
[CategoriesId] int NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Products_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [AspNetUsers] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_Products_Categories_CategoriesId] FOREIGN KEY ([CategoriesId]) REFERENCES [Categories] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Products_Categories_CategoriesId' on table 'Products' 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.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:e7a38f43-b64e-4c75-a5d7-dea3e0c6042c
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Products_Categories_CategoriesId' on table 'Products' 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.
Reason
Form the error message, we find the Products entity is related to the Users and Categories entities through the UserId and CategoriesId foreign keys, and Transactions is related to Products and Users. When trying to delete one of the entities, EF Core may encounter multiple cascading delete paths, resulting in conflicts.
So you can try to fix by using below settings.
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<Products>()
.HasOne(p => p.User)
.WithMany(u => u.Products)
.HasForeignKey(p => p.UserId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<Transactions>()
.HasOne(t => t.Users)
.WithMany(u => u.Transactions)
.HasForeignKey(t => t.UserId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<Transactions>()
.HasOne(t => t.Products)
.WithMany(p => p.Transactions)
.HasForeignKey(t => t.ProductsId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<Products>()
.HasOne(p => p.Categories)
.WithMany(c => c.Products)
.HasForeignKey(p => p.CategoriesId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<Categories>()
.HasOne(c => c.Users)
.WithMany()
.HasForeignKey(c => c.UsersId)
.OnDelete(DeleteBehavior.Restrict);
}
Then run the command to update the database.
add-migration test //[optional]-context WebApplication5Context
update-database //[optional]-context WebApplication5Context
7