• Product Information
  • About
  • News
  • Screenshots
• Support Information
  • Documentation
  • FAQ
  • Support
  • Upgrade policy
• Download
• Purchase

Download Buy

SQLite Code Factory online Help

CREATE TABLE

A CREATE TABLE statement is basically the keywords "CREATE TABLE" followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "sqlite_" are reserved for use by the engine.

Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. SQLite is typeless. The datatype for the column does not restrict what data may be put in that column. All information is stored as null-terminated strings. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT.

Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible to the process that opened the database and is automatically deleted when the database is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.

The optional conflict-clause following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. The default is abort ABORT. Different constraints within the same table may have different default conflict resolution algorithms. If an COPY, INSERT, or UPDATE command specifies a different conflict resolution algorithm, then that algorithm is used in place of the default algorithm specified in the CREATE TABLE statement. See the section titled ON CONFLICT for additional information.

CHECK constraints are ignored in the current implementation. Support for CHECK constraints may be added in the future. As of version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all work.

There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes. (This limit can be increased to 16MB by changing a single #define in the source code and recompiling.)

The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.

The exact text of each CREATE TABLE statement is stored in the sqlite_master table. Every time the database is opened, all CREATE TABLE statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then an equivalent CREATE TABLE statement is synthesized and store in sqlite_master in place of the original command. The text of CREATE TEMPORARY TABLE statements are stored in the sqlite_temp_master table.