Is it possible that one can add logic to a MySQL Table so that MySQL Table only allows insertion if certain conditions are met.
Say we have fields X1, X2, X3, X4
Can one then add logic to this Table such as:
Only allow INSERT IF X1 or X2 or X3 or X4 are NOT NULL.
Otherwise reject the INSERT.
Can such a logic be added to a MySQL Table and how?
FYI, we need to URGENTLY do this since someone is hacked one of our main Table and is
able to add data to it and I have blocked all such INSERT via the Php pages that we use
to to make such an INSERT, so that is want I want to add this logic on MySQL Table
level if possible.
Ayayayay!
Yes, I need to add a IF THEN to a MySQL Table, so that for example action INSERT is only allowed IF certain conditions are met.
And for reason listed I was really hoping that such logic could be added on the Table level.
Man this is really a sorely needed feature for MySQL, that is for MySQL developed to be able to add Table level logic which would
then give the Table iron clad protection from a bad Php programmer, or hacker, from messing up with the Table.
No of course the solution is not as simple as NOT NULL.
That was just an example, and in that example the fields could be NULL individually, but NOT all of them at the same time.
A check constraint does what it says - it checks a condition (constraint) is met and if it is, the insert/update is carried out.
(requiring a foreign key to have a corresponding record in another table is a constraint, but that depends on which engine you are running with MySQL)
You could of course write a trigger to do the work for you.
A trigger is a bit of code associated with a table that can be run before or after an insert, update or delete. you can write all sorts of constraints to be checked that way (no more than five people called john in the table for example!)
Google will help you learn about triggers in mysql.
Or read the online MySQL manual
