So before I start I dont want to get into any religious debates on if this is a feature, a quirk or something else. The goal here is to identify areas where SQLite behaves differently than either other DB systems or just in an unexpected/unanticipated way.
So starting with the biggies:
1) Limited Database enforced Type Safety:
CREATE TABLE IF NOT EXISTS Book
(
BookISBN varchar(14) primary key
,Name varchar(200)
,PublisherId int
);
INSERT INTO BOOK(BookISBN,Name)
VALUES(
"ReallyLongValueThatShouldCauseAProblem"
, "CSharpHacker SQLite Manual");
Most DBs honor the restrictions of the character lengths. SQLite will record the information you submit to it – even it is technically breaks your DDL definitions. More about this can be read about it Datatypes In SQLite Version 3. What should specifically be called out is:
Any column in a version 3 database, except an INTEGER PRIMARY KEY column, may be used to store any type of value.
So a more outlandish example would be:
DROP TABLE IF EXISTS Issue;
CREATE TABLE IF NOT EXISTS Issue
(
IssueId int
,Description varchar(200)
);
INSERT INTO Issue(IssueId,Description)
VALUES(
"ReallyLongTextValueThatShouldCauseAProblem"
, "CSharpHacker SQLite Manual");
SELECT * from Issue;
If you run the above you get “ReallyLongTextValueThatShouldCauseAProblem|CSharpHacker SQLite Manual”, with no errors!
Is that the sound of DBA’s running away screaming in horror…? Believe it or not this was intentional, and ‘sometimes’ sensible 
. I’ll cover a little more on that later, but lets go full circle on this.
Underneath the covers there is a 64 bit row identifier attached to every row, but if you want to use your own you can override that to use your own values. If you do this then this column categorically has to be an INTEGER value. So looking at the SQL below you can see we have now changed the type from ‘int’ to ‘INTEGER PRIMARY KEY’. This now enforces you have a value that is an integer for this one column, and the below fails with a “SQL error near line xx: datatype mismatch”.
DROP TABLE IF EXISTS Issue2;
CREATE TABLE IF NOT EXISTS Issue2
(
IssueId INTEGER Primary key
,Description varchar(200)
);
INSERT INTO Issue2(IssueId,Description)
VALUES(
"ReallyLongTextValueThatShouldCauseAProblem"
, "CSharpHacker SQLite Manual");
SELECT * from Issue2;
So one final warning “INTEGER PRIMARY KEY” is the same as “integer primary key”, but not “int primary key”. If you are wanting to override the internal 64 bit row identifier you have to use “integer” and not “int”, “bigint”, “short integer”. Thats an easy one to miss! Final quirk is that the “INTEGER PRIMARY KEY” needs to be exactly that or “INTEGER PRIMARY KEY ASC” (case insensitive), but “INTEGER PRIMARY KEY DESC” will not use the internal ID.
2. Only one process can write to the DB at the same time
The locking in SQLite is not fine grained! If anyone is writing to the DB it locks everyone and everything else out of the DB. All readers and writers are blocked until the pending write is done. So if you are expecting a lot of concurrent writes to the database that may need to be interleaved with some reads from other threads/processes you may experience some blocking. In reality it shouldnt matter too much as the writes are normally speedy. It should also be noted that in SQLite v2 there was a possibility of writer starvation where so many reads are going on the writer was never able to lock the file to perform the write – this has been addressed in SQLite V3.
3. Limited support for ALTER TABLE
Now I bet this has driven off the DBA’s now! Because of its heritage SQLite team hasn’t ever needed to support the more complex data migrations supported by other database products. You can use to add a column to the end of a table or to change the name of a table. However if you want to make more complex changes in the structure of a table, you have to recreate a new table and then copy the contents of your old table into the new one.
4. Foreign Keys are ‘recorded’ but not enforced
Technically the same thing can be achieved using the ‘Triggers’, but you need to be aware that these are ‘soft RI’ keys and are not enforced by the database engine.
5. Support for UPSERT via “REPLACE” or “INSERT OR REPLACE”
For MySQL users they are familiar with REPLACE, SQLite has aliased REPLACE to its native “INSERT OR REPLACE’.
So if you are still reading that is good! This is definitely the scary page that drives a few DBA’s into fits, but its sensible to know what you are using and its behaviors before it surprises you.
Related Links:
- SQLite for C# – Part 1 – Am I allowed to use it?
- SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
- SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
- SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
- SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
- SQLite for C# – Part 6 – SQLite Connection String Definitions
- SQLite for C# – Part 7 – Building SQLite.Net from source
- SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line