Database INSERT changes NULL value to empty string

I have a field in one of my database tables, careplanner_id, smallint(5), that is set as a foreign key. It allows NULL values, and its DEFAULT is set to NULL.

When I try to insert a record into the table with an empty form field for careplanner_id, CodeIgniter set its value in its mySQL query to an empty string instead of NULL, and then my foreign key designation fails.

Is this something CodeIgniter does, or should I be looking for a solution somewhere else, or should I just give up with the foreign key designation and deal with changes manually?

An empty form field == an empty string. If that's what you pass to CI, that's what it will insert.

Thanks. So for a FK to an id integer value, if the form select for careplanner_id is to be not selected, which would be best to use - an empty string, NULL or 0? I need to go back and change my CodeIgniter Form Helper option value for this case.

An empty form field == an empty string. If that's what you pass to CI, that's what it will insert.

Thank you. Are you sharp or what? By the way, only found this thread through a Bing search. The forum search function was unable to find for me.

Try setting your default value to 0 ( zero ).

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Careful with zero. If I'm logging temperatures for example in my webapp, then a zero implies that I recorded zero as opposed to leaving it NULL or blank. Another example: zero inches of snow were recorded or NULL inches were recorded? Difference.

Not a problem here. The issue is with foreign key id's that don't have to be entered. I'm finding that even if I set the default to NULL, a 0 is entered, so I decided to stick with 0.

Not a problem here. The issue is with foreign key id's that don't have to be entered. I'm finding that even if I set the default to NULL, a 0 is entered, so I decided to stick with 0.

Now that you say this, I think there's another side to it - unless I'm mistaken, you cannot have a foreign key reference to a NULL "value".

I'm quoting that word on purpose, because NULL is in fact not a value, but a token for lack of value and therefore a foreign key doesn't make sense - a reference can't point to nothing. This is not limited to databases, but there are a lot of other instances in DB programming where it becomes obvious, like COUNT(field_name) ignoring rows where field_name is NULL.