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.
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.
(03-04-2016, 01:07 PM)webmachine Wrote: 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. |
Welcome Guest, Not a member yet? Register Sign In |