I have an SQL server database that looks like this.
As you can see there are multiple cascade paths going into tblProblem and tblChat which results in me getting this error first with tblProblem.
Introducing FOREIGN KEY constraint 'FK_tblProblem_tblRegistration_RegistrationID' on table 'tblProblem' 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.
So I found out this is a limitation in SQL Server when we have multiple foreign keys going into the one table.
I am not certain how to get past this limitation and retain the desired functionality which is to ensure the Problem table can have way of displaying which user created the table.
That meant linking foreign key in tblProblem from the registered user who created the problem record via, (RegistrationID).
If I set the cascade path I’m getting the error in to have no action then I’m left with orphaned records in the child table (tblProblem) when I preform a delete in the parent table, tblRegistration.
My only foreseeable option is to delete RegistrationID foreign keys in tblChat and tblProblem. But then I can’t tell who created a “chat” record about the problem or a “problem” record in the first place.
My question is can I somehow in EF Core find out which user made an entry in tblProblems and tblChats without having to reference their RegistrationID in those tables, please?