This topic is locked
[SOLVED]

 Handling Constraints

2/14/2019 5:34:47 PM
PHPRunner General questions
M
MikeT author

Say you have a table Categories and a table things. Each record in Categories can have many child records in Things, 1:n relation between Categories and Things, the relation is from Categories-Primary-Key to a foreign-key field in Things.

I've setup this structure in mySQL and set a constraint for relational integrity in mySQL, so that you cannot delete a record in Categories when there are still related records in Things.
Now I've read that you can handle this with Events in phpr, by issuing a sql query in the before delete event to see if there are any related records, and if yes, to deny the delete.

For my case I need to ensure this also at the database level with the above mentioned constraints.
Here comes the question: is it possible to just work with the errors that result from the constraints and omit having to write the Event code (see above)?

So: let the db-engine do the constraint check and if there's a violation, catch the error in phpr and move on from there?

In my test I just get a generic php error page with the constraint in place (and no event code yet).
I guess I'll have to do both: write the constraints (as a last line of defense) and still do the pre-checks via events.
Thanks!

admin 2/15/2019

If your question is about avoiding writing the code to handle constraints - the answer is no, you will have to write some code. Either way you either need to write code to check constraints manually or analyze error message and do something else. Check constraints in your code is more flexible and neat approach.
Adding constraints on database level is an extra protection also makes sense.

M
MikeT author 2/16/2019

Thanks!
Is there already an API or example how to catch constraint/mySQL errors in a table event (e.g. before delete)?

I think there's DB::LastError(), but it seems I'd have to parse this for the error no. I'm probably too influenced by former systems/frameworks I used...I'm thinking about an error object with a number property for example.
Michael

admin 2/16/2019

You will have to parse error message/number no matter what framework you use or if you code everything manually. This why verifying everything before sending commands to the database is a better approach.

M
MikeT author 2/17/2019

Thanks, I'm still spoiled by native (non-web) db-coupled frameworks I guess;-)