SQLite Frequently Asked Questions Small. Fast. Reliable. Choose any three. Home Menu About Documentation Download License Support Purchase Search About Documentation Download Support Purchase Search Documentation Search Changelog Frequently Asked Questions How do I create an AUTOINCREMENT field? What datatypes does SQLite support? SQLite lets me insert a string into a database column of type integer! Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table? Can multiple applications or multiple instances of the same application access a single database file at the same time? Is SQLite threadsafe? How do I list all tables/indices contained in an SQLite database Are there any known size limits to SQLite databases? What is the maximum size of a VARCHAR in SQLite? Does SQLite support a BLOB type? How do I add, delete or rename columns from an existing table in SQLite? I deleted a lot of data but the database file did not get any smaller. Is this a bug? Can I use SQLite in my commercial product without paying royalties? How do I use a string literal that contains an embedded single-quote (') character? What is an SQLITE_SCHEMA error, and why am I getting one? I get some compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality? Case-insensitive matching of Unicode characters does not work. INSERT is really slow - I can only do few dozen INSERTs per second I accidentally deleted some important information from my SQLite database. How can I recover it? What is an SQLITE_CORRUPT error? What does it mean for the database to be "malformed"? Why am I getting this error? Does SQLite support foreign keys? I get a compiler error if I use the SQLITE_OMIT_... compile-time options when building SQLite. My WHERE clause expression column1="column1" does not work. It causes every row of the table to be returned, not just the rows where column1 has the value "column1". How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated? The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug? What is the Export Control Classification Number (ECCN) for SQLite? My query does not return the column name that I expect. Is this a bug? Where did my database go? (Or: How did my database become empty?) (1) How do I create an AUTOINCREMENT field? Short answer: A column declared INTEGER PRIMARY KEY will autoincrement. Longer answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. Or, if the largest existing integer key 9223372036854775807 is in use then an unused key value is chosen at random. For example, suppose you have a table like this: CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER ); With this table, the statement INSERT INTO t1 VALUES(NULL,123); is logically equivalent to saying: INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation. Note that the integer key is one greater than the largest key that was in the table just prior to the insert. The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration. Then the key chosen will be one more than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then the INSERT will fail with an SQLITE_FULL error code. (2) What datatypes does SQLite support? SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL. (3) SQLite lets me insert a string into a database column of type integer! 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.) But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity. (4) Why doesn't SQLite allow me to use '0' and '0.0' as the primary key on two different rows of the same table? This problem occurs when your primary key is a numeric type. Change the datatype of your primary key to TEXT and it should work. Every row must have a unique primary key. For a column with a numeric type, SQLite thinks that '0' and '0.0' are the same value because they compare equal to one another numerically. (See the previous question.) Hence the values are not unique. (5) Can multiple applications or multiple instances of the same application access a single database file at the same time? Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems. We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once. However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions. (6) Is SQLite threadsafe? Threads are evil. Avoid them. SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out. SQLite is threadsafe because it uses mutexes to serialize access to common data structures. However, the work of acquiring and releasing these mutexes will slow SQLite down slightly. Hence, if you do not need SQLite to be threadsafe, you should disable the mutexes for maximum performance. See the threading mode documentation for additional information. Under Unix, you should not carry an open SQLite database across a fork() system call into the child process. (7) How do I list all tables/indices contained in an SQLite database If you are running the sqlite3 command-line access program you can type ".tables" to get a list of all tables. Or you can type ".schema" to see the complete database schema including all tables and indices. Either of these commands can be followed by a LIKE pattern that will restrict the tables that are displayed. From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_SCHEMA". Every SQLite database has an SQLITE_SCHEMA table that defines the schema for the database. The SQLITE_SCHEMA table looks like this: CREATE TABLE sqlite_schema ( type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT ); For tables, the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command: SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name; For indices, type is equal to 'index', name is the name of the index and tbl_name is the name of the table to which the index belongs. For both tables and indices, the sql field is the text of the original CREATE TABLE or CREATE INDEX statement that created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the sql field is NULL. The SQLITE_SCHEMA table cannot be modified using UPDATE, INSERT, or DELETE (except under extraordinary conditions). The SQLITE_SCHEMA table is automatically updated by commands like CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX. Temporary tables do not appear in the SQLITE_SCHEMA table. Temporary tables and their indices and triggers occur in another special table named SQLITE_TEMP_SCHEMA. SQLITE_TEMP_SCHEMA works just like SQLITE_SCHEMA except that it is only visible to the application that created the temporary tables. To get a list of all tables, both permanent and temporary, one can use a command similar to the following: SELECT name FROM (SELECT * FROM sqlite_schema UNION ALL SELECT * FROM sqlite_temp_schema) WHERE type='table' ORDER BY name (8) Are there any known size limits to SQLite databases? See limits.html for a full discussion of the limits of SQLite. (9) What is the maximum size of a VARCHAR in SQLite? SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there. And it will keep all 500-million characters intact. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardles…