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&gt; .schema mytable
CREATE TABLE mytable (
    id              integer primary key,   /* 0 */
    mycol           integer not null,      /* 1 */
);

sqlite&gt; 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.