I understand that you do not want to break compatibility. But couldn't a PRAGMA STRICT_SQL (or the like) be introduced that would force to
a) reject CREATE statements with unknown declarations (I often use "STRING" for the datatype, leading to hard-to-find problems as SQLite uses a numeric type for such a column) b) reject INSERT (or the like) statements with data that do not match the declaration ----- Original Message ----- From: Richard Hipp <d...@sqlite.org> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Thursday, June 28, 2018, 02:02:26 Subject: [sqlite] column types and constraints On 6/27/18, Mark Wagner <m...@google.com> wrote: > I recently pointed out that sqlite doesn't enforce type names and > constraints when creating tables but I was unable to explain/justify this > behavior. I'm sure this has come up before and there's a clear answer but > I didn't find it easily. > For example this is accepted without error: CREATE TABLE bar2 (x happy > days); In the early days of SQLite, the goal was to get it to parse the CREATE TABLE statements of as many different SQL engines as possible. I looked at the supported datatypes of contemporary engines, and they were all different. So to maximize compatibility, I made the decision to mostly ignore the "type" and accept any sequence of identifiers as the type. The actual type used it computed according to the following rules, in order: (1) If the type name contains "INT" then use INTEGER (2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT (3) If the type name contains "BLOB" then use BLOB (4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL (5) Otherwise use NUMERIC Those rules are defined here: https://www.sqlite.org/datatype3.html#affname This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you store a string '3456' into an INT column, it converts the string into an integer, but if you store a string 'xyzzy' in an INT column it will actually store the string value. After the above decisions were made, SQLite became the most widely used database engine on the planet and over a trillion SQLite database files got created, and now we need to stick with that original idea lest we cause compatibility issues for all that legacy. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
