Search code examples
databasesqlitetextintegersqldatatypes

Sqlite column is getting assigned wrong data type value

I have a table in sqlite with below schema:

CREATE TABLE message(ITEM_ID TEXT, COUNTRY_ID TEXT, CODES INTEGER);

Even CODES is integer, still I am able to insert string data into the table like this:

insert into message values(124,'5','R')

I was expecting error from sqlite. Shouldn't it return a error ?

Thanks,

Solution

  • SQLite does not restrict types in that way. The SQLite FAQ has this to say;

    SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column.

    This deviates from (most) other SQL databases and as you may have noticed takes some extra care in testing your database code.