Thanks for all the good background. FWIW this came up because someone had created a row with something like: (column_name non null). Needless to say, this created a column without a "not null" constraint.
On Wed, Jun 27, 2018 at 5:02 PM Richard Hipp <d...@sqlite.org> wrote: > 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
