CODEWITHSUNDEEP
sqliteboolean
alanzo
alanzo

Reputation: 55

Boolean datatype accepting string value and integer value

Table having boolean column ,boolean should only accept value 0 or 1 but it is accepting int value as well as string value.

This is table schema - 

CREATE TABLE maintable_slave ( din INTEGER, strip_no integer, strip_status boolean);

And here is query through which i am updating column-

update maintable_slave set strip_status = 5 where din = 1;

update maintable_slave set strip_status = 'add' where din = 1;

here strip_status boolean column accepting string and integer value.

Upvotes: 2

Views: 2339

Answers (2)

laurent
laurent

Reputation: 90853

SQLite uses dynamic types, which means any values can be stored in any column. Regarding the type of a column, SQLite documentation says that:

The important idea here is that the type is recommended, not required. Any column can still store any type of data.

Upvotes: 0

Talya
Talya

Reputation: 19357

This is a frequently asked question—it's literally in SQLite's FAQ:

(3) SQLite lets me insert a string into a database column of type integer!

This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Any data can 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. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.

Upvotes: 4

Related Questions