One to one to many causes cycles or multiple cascade paths error


I'm using Entity Framework Core 3.1.0 together with SQL Server Express during development.

I have a one to one relation and a one to many relation connected like this:

Relation --> SupplierSettings --< Conditions

So between Relation and SupplierSetting I have a one to one relation. Between SupplierSetting and Conditions I have a one to many relation.

Excerpts of the classes are like this.

public class Relation
    public string GLN { get; set; } 
    public string Name { get; set; }
    public string Country { get; set; } 

public class SupplierImportSetting 
    public Relation Supplier { get; set; }
    public int SupplierId { get; set; }

    public int MinimumMarginPercentage { get; set; }
    public bool OnlyImportWithConditions { get; set; }

    public ICollection<SupplierCondition> Conditions { get; set; }

public class SupplierCondition 
    public SupplierImportSetting SupplierImportSetting { get; set; }
    public int SupplierImportSettingId { get; set; }

    public string DiscountGroup { get; set; }
    public string SupplierTradeItemCode { get; set; }
    public string Description { get; set; }

    public decimal? Discount1Percentage { get; set; } // 1 = 100%

I configured my context like this:

// One to one where Relation is Principal and SupplierImportSetting is dependent.
    .WithOne(sis => sis.Supplier)
    .HasForeignKey<SupplierImportSetting>(sis => sis.SupplierId)

// One to many with SupplierImportSetting as Principal and SupplierCondition as dependent.
    .HasMany(sis => sis.Conditions)
    .WithOne(c => c.SupplierImportSetting)
    .HasForeignKey(c => c.SupplierImportSettingId)

But I get this error:

Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

ALTER TABLE [SupplierConditions] ADD CONSTRAINT [FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId] FOREIGN KEY ([SupplierImportSettingId]) REFERENCES [SupplierImportSettings] ([Id]) ON DELETE CASCADE;

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId' on table 'SupplierConditions' 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, Action1 wrapCloseInAction) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1591
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) in E:\agent1_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 618
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in E:\agent1_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1169
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in E:\agent1_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1719
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) in E:\agent1_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2857
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 1395
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 974
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 contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_SupplierConditions_SupplierImportSettings_SupplierImportSettingId' on table 'SupplierConditions' 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.

Actually, SQL Server is probably right, because when I delete a Relation entity, this will cause a cascading cascading delete. But this is what I want. Relation is the principal, so the other records should be deleted.

So I figured, maybe I need to define the other way of the one to one relation as well. When I add this to my context config, the migration runs and executes without error. But when I check the database, the constraint that has been created on SupplierImportSettings does not have ON DELETE. So, when I remove a Relation entity, the SupplierImportSettings will not be removed. That's not what I want.

// SupplierImportSetting is principal, Relation is dependent.
// Define one to one the other way to disable cascade delete in this direction.
    .HasOne<Relation>(sis => sis.Supplier)

No matter what I try, I can't get Entity Framework Core to create the situations that I want.

asked on Stack Overflow Feb 5, 2020 by rolandow • edited Feb 5, 2020 by marc_s

2 Answers


I see that the way how you have configured relations will cause the deletion of conditions when a supplierSetting is deleted. It will cause the deletion of other supplierSettings which is wrong. Probably this is the reason why it fails.

// Try this change

    .HasMany(sis => sis.Conditions)
    .WithOne(c => c.SupplierImportSetting)
    .HasForeignKey(c => c.SupplierImportSettingId)
answered on Stack Overflow Feb 5, 2020 by Getson Cela

After many hours of debugging we finally found the answer.

While Relation, SupplierImportSetting and SupplierCondition didn't show a primary key to yet another involved table, it turned out that there was another table which I forgot about. Really annoying that MS SQL is able to detect that multiple cascade paths are present, but doesn't tell us which path is conflicting.

This is how we finally found out though. When in SQL Management Studio, expand the table where the error is coming from. In my case this was Supplier Condition. Then open the Keys 'folder'. Then double click on the key icon in front of the foreign key. This will open a new popup with Foreign Key Relationships. This window does not only show the foreign keys to other tables, but also show the foreign keys from other tables to this one.

enter image description here

So I added a DeleteBehaviour NoAction on this relation, and then the problem was gone. I still had the cascade deletes as I intended.

answered on Stack Overflow Feb 6, 2020 by rolandow

User contributions licensed under CC BY-SA 3.0