enum type and default value

[cgraz]

I have an enum field that is set up like this:

force_download enum('no','yes') NOT NULL default 'no',

I'm using checkboxes to handle this on a dynamic insert/update script. The value for the checkbox is 'yes', so it if it checked, the insert/update will update that field to equal yes in the database. If it is unchecked, I am having my form return an empty value. So my update looks like this:

UPDATE table ... force_download = '', another_field = 'value'

When I have force_download = '', I want it to save in my database as what the default value is, in this case, 'no'. Currently, it's just storing a blank value.

I can't hard code this as my checkbox values and defaults will vary. I need the database to have a value of 'no' rather than being empty. I have even tried

UPDATE table ... force_download = NULL, another_field = 'value'

and that didn't work either. Is there a way to force mysql to use my default value on updates and inserts? This works fine for inserts.

[bpat1434]

mySQL 4.1 -> 5.1:

If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the data type and the warning count is incremented. The default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.

So setting it to null won't help you. Try this:

UPDATE table ... force_download=DEFAULT, another_field = 'value'