We’re implementing a bulk update API for case management in Mendix 9.18 that allows external systems to update multiple cases in a single request. The API works fine for simple field updates, but fails when updates involve related entities.
When trying to update 50 cases with new assignee references, we get:
Error: Foreign key constraint violation
Cannot update Case.AssigneeId - referenced User does not exist
The strange part is that the User entities DO exist - we can query them successfully before the bulk update. It seems like the foreign key validation happens before all objects are committed, causing referential integrity issues. How should we handle bulk updates with foreign key relationships in Mendix REST APIs? Should we use a different transaction scope or validation approach?
The 207 Multi-Status approach sounds ideal for our use case. How do I structure the response to include both successful and failed updates? And should I still use a single transaction, or process each case independently?
Don’t commit individually - that’s a performance killer and loses atomicity. Instead, pre-validate all references before starting updates, then batch commit in the correct dependency order. First commit any new referenced entities, then commit the cases that reference them. Use a validation microflow that checks all foreign keys exist before processing any updates.
For bulk operations, consider implementing partial success with detailed error reporting. Not all 50 cases should fail because one has a bad reference. Return HTTP 207 Multi-Status with individual results for each case - some succeed, some fail with specific error messages. This gives the calling system much better visibility into what worked and what needs to be retried.