Text string stored in SQLite Integer column?

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?

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.

StackExchange Database Administrators Q#106364, answer score: 18