Text string stored in SQLite Integer column?

Question

I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line:

sqlite> .schema mytable
CREATE TABLE mytable (
 id integer primary key, /* 0 */
 mycol integer not null, /* 1 */
);
sqlite> SELECT mycol FROM mytable;
here is some text
here is more text
[...]
it's text all the way down

I'm confused. What gives?

Answer 1

This is a well known "quirk" of SQLite.

SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite.

Take a look here:

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 statements 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.

The advantages of this system are outlined here.

Note: The datatype limitations can be cumbersome, especially if you add time durations, or dates, or things of that nature in SQL. SQLite has very few built-in functions for that sort of thing. However, SQLite does provide an easy way for you to make your own built-in functions for adding time durations and things of that nature,

I think the point of SQLite is a bit like the quote about the C programming language -

"C allows you to do very stupid things because it also allows you to do very clever ones."

Same goes for SQLite.

Check out the entire StackOverflow thread referenced above.

The datatype in SQLite is more of a "hint" than a command.

• dan04: Note that if you need strict typing, you can always add a constraint like CHECK(typeof(mycol) = 'integer').
• Steve Day: The magic happens when you sort on an sqlite field that has been declared as integer yet has integers and text stored in it ...it sorts properly! sorting the numbers first in numerical order and then the Alpha in alpha order....superb!!!
• Vitas: well, wouldn't it be nice if they actually mentioned this in the official documentation?? Jeez... tutorialspoint.com/sqlite/sqlite_data_types.htm Seems like they want to keep this secret until people find out?
• Captain Man: @Vitas the official documentation is very forward about this. In the first paragraphs. 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." the rest of the page goes on to describe the ins and outs of it.