Not deleting child records with misformed FK

BIZ TMS Aurelius
1

I have this code

while Rezept.TBLREZEPTZUTATList.Count > 0 do begin
     Zutat := Rezept.TBLREZEPTZUTATList[0];
     mgrManager.Remove(Zutat);
     Rezept.TBLREZEPTZUTATList.Remove(Zutat);
end;
mgrManager.Remove(Rezept);

This works usually ok. However, I tracked the following problem down:
REZEPT is the master Record.
REZEPTZUTAT are child records. They have (among others) a FK relationship to TBLLEBENSMITTEL conssting of LEBENSMITTELCD and HERKUNFTCD.

For one record these values were malformed, so that the FK pointed to a nonexistent record. This was, because I did some recoding while FK ckecking was disabled and the recondig data contained this error.

When I tried to delete the master record of this malformed child with the code above, I got the error

Project RezeptSchnittstelle.exe raised exception class EAureliusOdbcException with message 'Error -1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Die DELETE-Anweisung steht in Konflikt mit der REFERENCE-Einschränkung 'FK_TBLREZEPTZUTAT_TBLREZEPT_REZEPTID'. Der Konflikt trat in der xxx-Datenbank, Tabelle 'dbo.TBLREZEPTZUTAT', column 'REZEPTID' auf.'.

So it seems, as if Aurelius does not fetch the child record, altough the relation master child is ok, but child grandchild is not. This makes a bit sense, but is somewhat unexpected.

Can you comment on this?

tx Bernd

2

I'm afraid I didn't fully understand what is going on.

The error message seems to indicate that you tried to delete a REZEPT record, but it was refused because there was an existing child REZEPTZUTAT pointing to that record.

3

Yes, thats correct. But why is this child record not fetched initially into the child list? And then not deleted in the loop which in turn lets the delete of the master record fail.

My finding is, that this is because of the malformed FK.

4

I don't know. To have an idea I'd need to see the whole mapping of involved etities, the exact data in the database, etc.. Ideally a sample project reproducing the issue.

5

Well, I tried to describe the problem as good as I can. Gathering this info will take some time.

Lucky me, I found what caused the problem.

Tx anyway.