Android SQLite: nullColumnHack parameter in insert/replace methods

Question

The Android SDK has some convenience methods for manipulating data with SQLite. However both the insert and replace methods use some nullColumnHack parameter which usage I don't understand.

The documentation explains it with the following, but what if a table has multiple columns that allow NULL? I really don't get it :/

SQL doesn't allow inserting a completely empty row, so if initialValues is empty, this column [/row for replace] will explicitly be assigned a NULL value.

Answer 1

Let's suppose you have a table named foo where all columns either allow NULL values or have defaults.

In some SQL implementations, this would be valid SQL:

INSERT INTO foo;

That's not valid in SQLite. You have to have at least one column specified:

INSERT INTO foo (somecol) VALUES (NULL);

Hence, in the case where you pass an empty ContentValues to insert(), Android and SQLite need some column that is safe to assign NULL to. If you have several such columns to choose from, pick one via the selection mechanism of your choice: roll of the dice, Magic 8-Ball(TM), coin flip, cubicle mate flip, etc.

Personally, I'd've just made it illegal to pass an empty ContentValues to insert(), but they didn't ask me... :-)