In SQLite the following statement would be successful and the string would be inserted/updated into the SALARY column which is of type INTEGER:
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?
12
No, dynamic typing requires both more storage space and more processing time, especially since they also add type affinity, meaning it has a preferred type that the programmer is free to ignore. It is truly an intentional feature with real trade off costs. Those costs are effectively negligible for the use cases SQLite targets, but they are still there.
The usefulness of such features is hard to see because you’re not accustomed to having it available to you. The workaround for its lack feels more natural to you now. Because of your prior experience, you’re thinking of it as an INTEGER field which shall never be anything else, but SQLite sees it more as a field of any type, but probably will mostly contain integers. Perhaps it is a ZIP code for a company that mostly does business in the U.S., but has a handful of Canadian customers. Allowing the user to specify an integer affinity will save a lot of space over making it a string in every row, but still gives you that option.
6