MySQL No null insert in required field

[tarsus]

I'm using MySQL 4.

I thought I understood that, if you have a default value specified for a field that cannot be null, using NULL in an insert query will result in that field receiving that default value.

I've always done this before. However, now I am receiving an error telling me the field cannot be null. The field most certainly has a default value set in the table.

On top of that, the reverse is true when I try my update queries. Using NULL works, but using DEFAULT results in an error. (But using NULL results in an entry of 0, not the default.)

If I can't use NULL in inserts or DEFAULT in updates, how do I even utilize the defaults I've set for required fields? I really should not have to assign these defaults in my server scripts when building the queries.

[aj_nsc]

from my understanding, if you want these fields to be their default value, then you don't declare a value for them in an insert or update query, you just define the values for the fields that you want to insert....

[tarsus]

That's fine, except for when I don't know whether the user has entered a value for the field or not. (This is a trivial field that I want the user to be able to leave blank, but which nevertheless should have a default value.)

Isn't there any other way to make use of my default values? Can anyone verify whether attempting to insert NULL should work or not?

[mattyblah]

I believe using "default" should work. Something like:

Insert Into <table> (<DefaultValueField>) Values (default)

[tarsus]

That works fine in an insert. But as I already said, it does not work in an update.

[nevart]

This is a simple problem to solve if you are using PHP to code your query...

Just do something like this:

/* We will make color a required field for this example */
if($color!=""){
$sql="INSERT INTO fruit (name, color) VALUES ('$name','$color');
} else {
$sql="INSERT INTO fruit(name) VALUES ('$name');
};

Same thing for updates. You just check for the value that is missing and don't include it. If you have a lot of fields to check then you would need something more complicated (create a $values variable and append it for each field that has data then pass that variable to VALUES).