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...
Code:
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...
Code:
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...
Code:
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...
Code:
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