Search code examples
databasesqlitetextintegersqldatatypes

SQLite Column restriction: Only Integer

The following code lines illustrates the creation of table and where age column is created with data type INTEGER but still able to insert TEXT.

sqlite> .open dummy.db
sqlite> CREATE TABLE dummy1 (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER);
sqlite> INSERT INTO dummy1(name, age) VALUES ("varma", 40);
sqlite> INSERT INTO dummy1(name, age) VALUES ("sandy", sixty);
Error: no such column: sixty
sqlite> INSERT INTO dummy1(name, age) VALUES ("sandy", "sixty");
sqlite> SELECT * FROM dummy1;
1|varma|40
2|sandy|sixty

I tried creation of table with column restriction as : "age INTEGER"

Attempt to restriction failed.

Solution

  • SQLite uses dynamic typing; if you want to enforce column types, you have to use an explicit constraint:

    CREATE TABLE dummy1 (
    ...,
    age INTEGER  CHECK (typeof(age) = 'integer')
);