Nulls have been the bane of my life as a database programmer and they got me again today. I was getting a constraint violation on an optional foreign key from MySQL.
My PHP data entry system was returning the empty string when the user hadn’t entered anything, which is fine. In Oracle (as I’ve discussed many times here) ‘’ is null. Not in MySQL. In MySQL it is an empty string – which is probably right.
I had to add the following scan in my processing:
foreach ($values as $key => $val)
if( $val == ’’) $values[$key] = null ;
Now it works.