SQLite table with integer column stores string

Question

I found something weired in my application. I created a table with a column named type which should store integers:

db.execSQL("CREATE TABLE " + CellColumns.TABLE + " ("
    + CellColumns._ID + " INTEGER PRIMARY KEY,"
    + CellColumns.TYPE + " INTEGER," // <-- this
    + CellColumns.CELL_ID + " INTEGER,"
    + CellColumns.CITY_ID + " INTEGER,"
    + CellColumns.LOAD + " INTEGER,"
    + CellColumns.ORIENTATION + " INTEGER);");

Reading:

String type = c.getString(c.getColumnIndex(CellColumns.TYPE));

But somehow I always store strings into it without any problems (seems I forgot that this column was meant for integers). Also reading the strings with a query works. Is this dynamic "type cast" of a column a feature of sqlite?

Answer 1

From http://www.sqlite.org/datatype3.html:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

So yes, this is a feature of sqlite. Read the whole page I linked, and tread carefully :)