C
C#

help

Error Introducing FOREIGN KEY constraint

AAlerin9/21/2022
An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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.
An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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.
Models:
public class Roles
{
public int Id { get; set; }
public User User { get; set; } = null!;
public Guid UserId { get; set; }
public Role Role { get; set; } = null!;
public int RoleId { get; set; }
}
public class Roles
{
public int Id { get; set; }
public User User { get; set; } = null!;
public Guid UserId { get; set; }
public Role Role { get; set; } = null!;
public int RoleId { get; set; }
}
public class Role
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public ICollection<RolePolicy> Permission { get; set; } = new List<RolePolicy>();
}
public class Role
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public ICollection<RolePolicy> Permission { get; set; } = new List<RolePolicy>();
}
public class RolePolicy
{
public int Id { get; set; }
public Role Role { get; set; } = null!;
public int RoleId { get; set; }
public string Policy { get; set; } = string.Empty;
}
public class RolePolicy
{
public int Id { get; set; }
public Role Role { get; set; } = null!;
public int RoleId { get; set; }
public string Policy { get; set; } = string.Empty;
}
public class User
{
[Key]
public Guid Id { get; set; }

[StringLength(16, MinimumLength = 4), Required]
public string Name { get; set; } = string.Empty;

...

public Role DisplayGroup { get; set; } = null!;
public int DisplayGroupId { get; set; }
public ICollection<Roles> Roles { get; set; } = new List<Roles>();
public class User
{
[Key]
public Guid Id { get; set; }

[StringLength(16, MinimumLength = 4), Required]
public string Name { get; set; } = string.Empty;

...

public Role DisplayGroup { get; set; } = null!;
public int DisplayGroupId { get; set; }
public ICollection<Roles> Roles { get; set; } = new List<Roles>();
Error:
Applying migration '20220921162638_v1'.
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Stand].[Identity.Roles] (
[Id] int NOT NULL IDENTITY,
[UserId] uniqueidentifier NOT NULL,
[RoleId] int NOT NULL,
CONSTRAINT [PK_Identity.Roles] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Identity.Roles_Identity.Role_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Stand].[Identity.Role] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_Identity.Roles_Identity.User_UserId] FOREIGN KEY ([UserId]) REFERENCES [Stand].[Identity.User] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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:3aebe78b-44e3-4096-9c65-6bbfb6f02560
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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.
Applying migration '20220921162638_v1'.
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Stand].[Identity.Roles] (
[Id] int NOT NULL IDENTITY,
[UserId] uniqueidentifier NOT NULL,
[RoleId] int NOT NULL,
CONSTRAINT [PK_Identity.Roles] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Identity.Roles_Identity.Role_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Stand].[Identity.Role] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_Identity.Roles_Identity.User_UserId] FOREIGN KEY ([UserId]) REFERENCES [Stand].[Identity.User] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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:3aebe78b-44e3-4096-9c65-6bbfb6f02560
Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Identity.Roles_Identity.User_UserId' on table 'Identity.Roles' 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 am I doing wrong?
public DbSet<Models.User> User => Set<Models.User>();
public DbSet<Models.Role> Role => Set<Models.Role>();
public DbSet<Models.RolePolicy> RolePolicy => Set<Models.RolePolicy>();
public DbSet<Models.Roles> Roles => Set<Models.Roles>();

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Model.GetEntityTypes().ForEach(entity => {
var name = modelBuilder.Entity(entity.Name).Metadata.ClrType.Name;
modelBuilder.Entity(entity.Name).ToTable($"Identity.{name}", "Stand");
});

base.OnModelCreating(modelBuilder);
}
public DbSet<Models.User> User => Set<Models.User>();
public DbSet<Models.Role> Role => Set<Models.Role>();
public DbSet<Models.RolePolicy> RolePolicy => Set<Models.RolePolicy>();
public DbSet<Models.Roles> Roles => Set<Models.Roles>();

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Model.GetEntityTypes().ForEach(entity => {
var name = modelBuilder.Entity(entity.Name).Metadata.ClrType.Name;
modelBuilder.Entity(entity.Name).ToTable($"Identity.{name}", "Stand");
});

base.OnModelCreating(modelBuilder);
}
AAlerin9/21/2022
If I delete User it works fine, why is it? Only with this model, other models work without any problems.

Looking for more? Join the community!