Is this a Bug?

saidbakr

I have MySQL server Version 4.0.12.
When I create a table and set the property of Not Null for one of its fields. However, when I insert data from the application and the form's element which assumed to handle the data which will be inserted in that field is empty, The data base accept this process and the new record appears to carry this field empty.
Is this a bug or my error in istalling MySQL.

Protector

Dunno about the sql thing. But I solved it by checking the value before inserting it to the table:

Ie:

<form method=post action=insert.php>
<input type=text name=value>
</form>
......

And befor we do the insert to the db vo do like this.

if($value == '')
{
echo "Cant post an empty value";
}
else
insert the value intop the table.

or just use a java script that force user to insert a value before submitting the form

saidbakr

Thank you
I think using serverside code to check the empty field is better than javascript (client side) because someone can post to the script page after removing javascript validation.

Sxooter

A couple of points. first, MySQL's handling of NULLs is not standard, see: http://sql-info.de/mysql/gotchas.html.

Next, a NULL is NOT the same as blank.

For a table created like this:
create table test (info text not null);

the insert statements:
insert into test (info) values ('');
insert into test (info (values ('NULL');
are perfectly legal. '' is NOT the same as NULL. If you're thinking NULL as in null terminated string or some other meaning, you need to realize that SQL NULL doesn't mean the same thing. In SQL it means there may or may not be a value, but we don't know what it is. Now, for the above created table, the insert statement:
insert into test (info) values (NULL);
is NOT legal. Note that 'NULL' is the string NULL, which has no special meaning, while NULL (without the single quotes) is the keyword NULL, which has the special meaning.

Now, on to other, stranger features of NULL. Since NULL represents an unknown value, NULL does not equal NULL. So, if we had a table with NULL values, we would use the syntax IS NULL to match those rows, not =NULL.

i.e.:
select from table where field=NULL;
should return nothing, while
select from table where field IS NULL;
will return all the rows where field is null.

saidbakr

🆒
Ok, so it's like boolean data type in other languages. i.e
$var = true leads to
if($var){
//this will be done
}
it's differ than $var = "true" which is regarded as a string value for that variable.

The same for sql, NULL can be regarded as data type while "NULL" is a string value.
Is this correct

Sxooter

Exactly correct. Another interesting keyword is DEFAULT. It's what's supposed to get used for things like autoincrements etc...

in pgsql land:

create table test (id serial primary key, info text default 'abc123');
insert into test (id, info) values (DEFAULT,'my text');
and
insert into test (info) values ('more text');
and
insert into info values (DEFAULT,'even more');

are all functionally equivalent.