Okay, I am officially FREAKED OUT by what I have been witnessing with my database this past week.
It seems like there is NO DATABASE INTEGRITY as far as MySQL honoring attributes like Data-Type, Field-Size, Nullable, etc.
Here are some examples…
Example #1: (Working on this one now, and not sure what to do?!)
I just added a new field to my “member” table to log how long it takes a User (or Bot) to register.
I have a field called “registration_time” that is a SMALLINT, Null=No.
As an error-check, I tried to INSERT a record where registration_time = 99999.
You would expect that to fail, but my PHP script ran just fine.
When I looked in the table with phpMyAdmin, registration_time = 0?!
What is going on??
Example #2:
In my “member” table is another field called “photo_approved” which is a TINYINT, Null=No, Default=0.
If I run this query…
UPDATE member
SET photo_approved = '8888', last_activity = now()
WHERE id = 57
The query runs without any errors, and photo_approved = 127
How is that possible?!
Example #3:
Similar to above, when I run this query…
UPDATE member
SET photo_approved = 'xxx', last_activity = now()
WHERE id = 57
…there are no errors.
And photo_approved = 0?!
Example #4:
In the “member” table, “id” is an Integer, Not Nullable, AutoIncrement, PK.
If I run this query…
UPDATE member
SET id=NULL, last_activity=now()
WHERE id=57;
…the query runs without issue. And id=0
Example #5:
In the “member” table, there is an “email” field, which is VARCHAR(80), Null=No, UK.
If I run this query…
UPDATE member
SET email=NULL
WHERE id=61
…it runs without error. And email=NULL
How can this happen?
Bottom-line is that MySQL seems to be letting my PHP script get away with murder as far as what gets put into the database?! If that’s the case, then why not make every field “TEXT” and call it a day?!
Sincerely,
Debbie