I'm switching from using a framework's model handling to using straight sql for the purpose of improving the performance. The framework would handle the model update and insert, and now I'm having a hard time with the validation aspect.
I'm updating a model with several relationships, the framework allows all queries to fail if one fails. I'm trying to emulate this with multiple queries.
How do I check validity of several tables before starting to write any data to the database for update and create functions? The added difficulty about the create queries is that the relationship tables need the id of the first one to be either created first, or validity of a key to be ignored on queries.
Normally you do this with a combination of constraints and transactions. Create PK, FK, and check constraints as needed. Possibly also create update timestamp triggers if you want to ensure you aren't overwriting a row someone else just modified. If an error is raised on the insert, update or delete, rollback the transaction (or handle it).