MySQL Strange Behavior !!!

[saidbakr]

To easily understand what's the problem, please look at the attached mysql.gif file to this contribution

There's a field named as prog_name, in a table has InnoDB table type.
this field is set to be Not Null,
on INSERT all things goes well, i.e MySQL refuse the entry if this field is null, i.e if I don't
write any thing in the field, it produce the following message
Column 'prog_name' cannot be null
Ok...
The problem in Update,
In update a row, if I changed the prog_name filed to blank, i.e nothing, null. MySQL accept
this without any word!!!
So, Please help me,
Is this a bug in MySQL?
Is this a bug in My Work?
Or What?
The attached image is so usefull to illustrating all field properties, through SQLyog screen.

OS Win_ME
MySQL Ver, 4.0.12

😕 😕 😕 😕 😕

[Sxooter]

First and foremost, nothing does NOT equal NULL. NULL means, for the most part, unknown. I.e. it has a value, we just don't know it yet. Blank, or nothing, is not the same thing. If you insert it this way, it should take it:

insert into table (not_null_field) values ('');

Whereas if you do it this way:

insert into table (not_null_field) values (NULL);

then it should fail.

If you insert into the row, without setting anything for a field, then a NULL gets inserted.

Stop thinking of '' and NULL being the same, they aren't, although some databases, like Oracle treat them the same, they are aren't the same at all.