worldnews — 2012-03-23T13:37:49-04:00 — #1
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.
r937 — 2012-03-23T13:52:05-04:00 — #2
declare the columns as NOT NULL
oh, wait... did you mean you want at least one of the values to be not null? or all of them?
if only one, then this logic needs to be implemented with php
davemaxwell — 2012-03-23T13:54:26-04:00 — #3
If there had to be a value, then set the fields to not accept null values.
ALTER TABLE t1 CHANGE x1 VARCHAR(20) NOT NULL;
dagnabbit - rudified again.....tha't what I get for ensuring the mySQL syntax didn't change.
worldnews — 2012-03-23T14:41:48-04:00 — #4
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.
worldnews — 2012-03-23T14:43:13-04:00 — #5
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.
r937 — 2012-03-23T14:47:13-04:00 — #6
so were our answers
r937 — 2012-03-23T14:50:41-04:00 — #7
by the way, what you're looking for is the CHECK constraint
all the complexity you need...
except it's not implemented in mysql... yet... that i know of
worldnews — 2012-03-23T15:22:08-04:00 — #8
What is "CHECK constraint"?
And how is that going to help us in this situation if it is not "implemented in mysql"!
dr_john — 2012-03-23T15:52:46-04:00 — #9
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
r937 — 2012-03-23T15:59:07-04:00 — #10
given the URGENT status of your problem, i would have to say close to zero
however, you did voice the desire to "add this logic on MySQL Table level if possible"
i thought, for your future reference, that you might want to learn about CHECK constraint because that's what it does
worldnews — 2012-03-26T13:25:56-04:00 — #11
triggers, sounds like exactly what I am looking for. I am on it.
wwb_99 — 2012-03-27T19:59:42-04:00 — #12
Why is your app so loosey-goosey that it is letting these UPDATEs through. Branching logic like this is best left above the data tier . . .