In SQLite the following statement would be successful and the string would be inserted/updated into the
SALARY column which is of type
update employee set salary='TOO MUCH' where emp_id=1;
Note that zero will not be inserted/updated but the actual "TOO MUCH" string, so this is not about authomatic type conversion.
The FAQ states:
This is a feature, not a bug. 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. 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.)
So this behaviour is clearly intentional, nevertheless I wonder why SQLite has this behaviour, since most other SQL databases I know of behave quite differently, they would rise an error, or convert the string 0, when trying to insert a non-numeric string into a numeric column.
Would the SQLite library be less useful without this behaviour?
Is this made so by design to keep the library small and fast?
Would the SQLite library be significantly slower or bigger in order to rise errors when triying to insert a string into a numeric column?