Frequently Asked Questions

(1) How do I create an AUTOINCREMENT field.

SQLite does not support AUTOINCREMENT. If you need a unique key for a new entry in a table, you can create an auxiliary table with a single entry that holds the next available value for that key. Like this:

CREATE TABLE counter(cnt);
INSERT INTO counter VALUES(1);

Once you have a counter set up, you can generate a unique key as follows:

BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;

There are other ways of simulating the effect of AUTOINCREMENT but this approach seems to be the easiest and most efficient.

New in SQLite version 2.2.0: If one of the columns in a table has type INTEGER PRIMARY KEY and you do an INSERT on that table that does not specify a value for the primary key, then a unique random number is inserted automatically in that column. This automatically generated key is random, not sequential, but you can still use it as a unique identifier.

Here is an example of how the INTEGER PRIMARY KEY feature can be used:

CREATE TABLE ex2(
  cnum INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);
INSERT INTO ex2(name,email) VALUES('drh','drh@hwaci.com');
INSERT INTO ex2(name,email) VALUES('alle','alle@hwaci.com');
SELECT * FROM ex1;

Notice that the primary key column cnum is not specified on the INSERT statements. The output of the SELECT on the last line will be something like this:

1597027670|drh|drh@hwaci.com
1597027853|alle|alle@hwaci.com

The randomly generated keys in this case are 1597027670 and 1597027853. You will probably get different keys every time you try this. The keys will often be ascending, but this is not always the case and you cannot count on that behavior. The keys will never be sequential. If you need sequential keys, use the counter implemention described first.

(2) SQLite lets me insert a string into a database column of type integer!

This is a feature, not a bug. SQLite is typeless. Any data can 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 is (mostly) ignored. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold an integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

Because SQLite ignores data types, you can omit the data type definition from columns in CREATE TABLE statements. For example, instead of saying

CREATE TABLE t1(
  f1 int,
  f2 varchar(10),
  f3 boolean
);

You can save yourself a lot of typing and formatting by omitting the data type declarations, like this:

CREATE TABLE t1(f1,f2,f3);

(3) Why does SQLite think that the expression '0'=='00' is TRUE?

This is a consequence of SQLite being typeless. All data is stored internally as a null-terminated string. There is no concept of separate data types for strings and numbers.

When doing a comparison, SQLite looks at the string on both sides of the comparison operator. If both strings look like pure numeric values (with no extra punctuation or spacing) then the strings are converted to floating point numbers using atof() and the results are compared. The results of atof("0") and atof("00") are both 0.0, so those two strings are considered to be equal.

If only one string in a comparison is a pure numeric, then that string is assumed to be less than the other. Of neither string is a pure numeric, then strcmp() is used for the comparison.

(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?

Every row much have a unique primary key. But 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.

You can work around this issue in two ways:

1. Remove the primary key clause from the CREATE TABLE.

2. Prepend a space to the beginning of every value you use for the primary key. The initial space will mean that the entries are not pure numerics and hence will be compared as strings using strcmp().

(5) My linux box is not able to read an SQLite database that was created on my SparcStation.

The x86 processor on your linux box is little-endian (meaning that the least signification byte of integers comes first) but the Sparc is big-endian (the most significant bytes comes first). SQLite databases created on a little-endian architecture cannot be used on a big-endian machine and vice versa.

If you need to move the database from one machine to another, you'll have to do an ASCII dump of the database on the source machine and then reconstruct the database at the destination machine. The following is a typical command for transferring an SQLite databases between two machines:

echo .dump | sqlite from.db | ssh sparc sqlite to.db

The command above assumes the name of the destination machine is sparc and that you have SSH running on both the source and destination. An alternative approach is to save the output of the first sqlite command in a temporary file, move the temporary file to the destination machine, then run the second sqlite command while redirecting input from the temporary file.

(6) 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. On unix systems, multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at once. On windows, only a single process can be reading from the database at one time since Win95/98/ME does not support reader/writer locks.

The locking mechanism used to control simultaneous access might not work correctly if the database file is kept on an NFS filesystem. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.

Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, MySQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using a big database server instead of SQLite.

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 sqlite_busy_handler() or sqlite_busy_timeout() API functions. See the API documentation for details.

(7) Is SQLite threadsafe?

Almost. In the source file named "os.c" there are two functions named sqliteOsEnterMutex() and sqliteOsLeaveMutex(). In the default distribution these functions are stubs. They do not do anything. If you change them so that they actually implement a mutex, then SQLite will be threadsafe. But because these routines are stubs, the default SQLite distribution is not threadsafe.

"Threadsafe" in the previous paragraph means that two or more threads can run SQLite at the same time on different "sqlite" structures returned from separate calls to sqlite_open(). It is never safe to use the same sqlite structure pointer simultaneously in two or more threads.

(8) How do I list all tables/indices contained in an SQLite database

If you are running the sqlite 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_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  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_master
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_MASTER table is read-only. You cannot change this table using UPDATE, INSERT, or DELETE. The table is automatically updated by CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands.

Temporary tables do not appear in the SQLITE_MASTER table. At this time there is no way to get a listing of temporary tables and indices.

(9) Are there any known size limits to SQLite databases.

Internally, SQLite can handle databases up to 2^40 bytes (1 terabyte) in size. But the backend interface to POSIX and Win32 limits files to 2^31 (2 gigabytes).

SQLite arbitrarily limits the amount of data in one row to 1 megabyte. There is a single #define in the source code that can be changed to raise this limit as high as 16 megabytes if desired.

There is a theoretical limit of about 2^32 (4 billion) rows in a single table, but there is no way to test this limit without exceeding the maximum file size, so it is not really an issue. There is also a theoretical limit of about 2^32 tables and indices, but again it is not really possible to reach this limit due to the file size constraint.

The name and "CREATE TABLE" statement for a table must fit entirely within a 1-megabyte row of the SQLITE_MASTER table. Other than this, there are no constraints on the length of the name of a table, or on the number of columns, etc. Indices are similarly unconstrained.

(10) How do I add or delete columns from an existing table in SQLite.

SQLite does not support the "ALTER TABLE" SQL command. If you what to change the structure of a table, you have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;