Quirks, Caveats, and Gotchas In SQLite

Where this ends up causing problems is when developers do some initial coding work using SQLite and get their application working, but then try to convert to another database like PostgreSQL or SQL Server for deployment. If the application is initially taking advantage of SQLite's flexible typing, then it will fail when moved to another database that is more judgmental about data types. Flexible typing is a feature of SQLite, not a bug. Flexible typing is about freedom. Nevertheless, we recognize that this feature does sometimes cause confusion for developers who are accustomed to working with other databases that are more strict with regard to data type rules. In retrospect, perhaps it would have been less confusing if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. … # 4. Foreign Key Enforcement Is Off By Default SQLite has parsed foreign key constraints for time out of mind, but added the ability to actually enforce those constraints much later, with version 3.6.19 (2009-10-14). By the time foreign key constraint enforcement was added, there were already countless millions of databases in circulation that contained foreign key constraints, some of which were not correct. To avoid breaking those legacy databases, foreign key constraint enforcement is turned off by default in SQLite. … # 5. PRIMARY KEYs Can Sometimes Contain NULLs A PRIMARY KEY in an SQLite table is usually just a UNIQUE constraint. Due to an historical oversight, the column values of a PRIMARY KEY are allowed to be NULL. This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that the decision was made to support the buggy behavior moving forward. You can work around this problem by adding a NOT NULL constraint on each column of the PRIMARY KEY. Exceptions: - The value of an INTEGER PRIMARY KEY column must always be a non-NULL integer because the INTEGER PRIMARY KEY is an alias for the ROWID. If you try to insert a NULL into an INTEGER PRIMARY KEY column, SQLite automatically converts the NULL into a unique integer. - The WITHOUT ROWID and STRICT features was added after this bug was discovered, and so WITHOUT ROWID and STRICT tables work correctly: They disallow NULLs in the PRIMARY KEY. … # 7. SQLite Does Not Do Full Unicode Case Folding By Default SQLite does not know about the upper-case/lower-case distinction for all unicode characters. SQL functions like upper() and lower() only work on ASCII characters. There are two reasons for this: 1. Though stable now, when SQLite was first designed, the rules for unicode case folding were still in flux. That means that the behavior might have changed with each new unicode release, disrupting applications and corrupting indexes in the process. 2. The tables necessary to do full and proper unicode case folding are larger than the whole SQLite library. … This misfeature means that a misspelled double-quoted identifier will be interpreted as a string literal, rather than generating an error. It also lures developers who are new to the SQL language into the bad habit of using double-quoted string literals when they really need to learn to use the correct single-quoted string literal form. In hindsight, we should not have tried to make SQLite accept MySQL 3.x syntax, and should have never allowed double-quoted string literals. However, there are countless applications that make use of double-quoted string literals and so we continue to support that capability to avoid breaking legacy. As of SQLite 3.27.0 (2019-02-07) the use of a double-quoted string literal causes a warning message to be sent to the error log. … The same SQL statement will fail on every other SQL implementation that we know of due to the use of keywords "union", "true", and "with" as identifiers. The ability to use keywords as identifiers promotes backwards compatibility. As new keywords are added, legacy schemas that just happen to use those keywords as table or column names continue to work. However, the ability to use a keyword as an identifier sometimes leads to surprising outcomes. For example: