Integrity constraint violation: foreign key no parent problem with 1:n composition

Support
#1

Hi all,

I have a problem with saving my entity hierarchy.
The data model has a company with 1:n employees and each employee has 1:n tasks. They are compositions.

Im my company edit screen I have a table with all employees. When I add a new employee, their edit screen opens and there I have a table with all tasks for the employee. So I add a new task as well for the new employee. When I hit ok for the task edit, it is committed to the parent data context. Then I hit ok on the employee edit and they are also committed to the parent data context.
Finally, when I hit ok/save in the company edit, I can see that the company, the new employee and the new task are all in the dataContext as modified instances.

But when the commit happens, I get a foreign key no parent error for the task because it has the employee as parent which is not yet saved to the database. In the data context, the task comes before the employee so the database gives me the error.

How can I influence the order in the dataContext? I always thought the modified instances get inserted in the data base and then the commit happens and only then the contraints are tested but I think I have been wrong there.

So the question would be how to handle those 1:n:n compositions? Do I need to commit the employee as soon as they are created? That maybe would work but then the user could not cancel the creation of the employee anymore.

Thanks for any hints! :slight_smile:

p.s. the error seems to be caused because I set the new task in the new employee also as a property “currentTask”, so the employee has a list of tasks but always one that is currently executed. But the WHY remains a mystery to me…

#2

Hi,

So you want to save both a new employee E1 that references a new task T1 and this task T1 that references the employee E1 in a single commit? I’m not sure this would work. Whatever entity is saved first, a FK constraint violation will happen. Maybe you can add the “isCurrent” boolean property to the Task entity instead of the reference to the employee? Or fill the “currentTask” reference in the entity changed listener after all new records are saved successfully.

#3

Hi Maxim,

Thank you for the reply. Yes, I think the circular referencing is the problem.
Your suggestions are good and usually I would use those but our customers… :smiley:

Having a flag “isCurrent” is the way I would do it usually but the table of employees should also show their current task and it should be sortable and groupable so I can’t use column generators to show the info.
I used transient properties in the Employee class in the past but had some weird effects when dealing with List properties in there with lazy loading and merging .

I now use you suggestion with the changed listener but in a hacky way I feel:
when opening the task editor, entering the new task and closing, I use the before commit event in the screen to set the “currentTask” property in the employee like before. Otherwise it will not be shown in the employee table because the real commit which triggers the entity listener only fires when the company editor is closed. So in the company editor I also use the before commit screen event and set the “currentTask” property to null in all new/modified employees in the dataContext. The data can now be committed and after that the entity listener will fire where I can set the property again.

It works although it feels a bit unstable. But if someone has the exact same problem, this might work for them :wink: