4

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?

9

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.

  • 1
    I don't suppose there's an obvious reason why someone would declare a column of type Integer for the exclusive purpose of storing strings? – igal Jul 8 '15 at 17:20
  • @igal Why do you assume that the creator of that database knew what he was doing? ☺ – CL. Jul 9 '15 at 11:19
  • @CL. - I can tell you that [D. Richard Hipp] (en.wikipedia.org/wiki/D._Richard_Hipp) is probably one of the most competent designer/programmers around. Why do you think that SQLite is the most widely used RDBMS (browsers, mobile phones). Take a look at what I wrote about C allowing you to do stupid things because it also allows you to do very clever ones. – Vérace Jul 9 '15 at 14:13
  • 1
    Note that if you need strict typing, you can always add a constraint like CHECK(typeof(mycol) = 'integer'). – dan04 Jul 14 '15 at 23:08
  • 1
    what is the purpose of setting types on columns then ? Giving an indication on what the column was supposed to store (without guarantee ?) – tigrou Jan 28 '19 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.