Create a table
CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name.]
table-name (
column-def [, column-def]*
[, constraint]*
)
CREATE [TEMP | TEMPORARY] TABLE [database-name.]
table-name AS select-statement
column-def = name [type] [[CONSTRAINT name] column-constraint]*
type = typename | typename ( number ) | typename ( number , number )
column-constraint = NOT NULL [ conflict-clause ] |
PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] |
UNIQUE [ conflict-clause ] |
CHECK ( expr ) |
DEFAULT value |
COLLATE collation-name
constraint = PRIMARY KEY ( column-list ) [ conflict-clause ] |
UNIQUE ( column-list ) [ conflict-clause ] |
CHECK ( expr ) [ conflict-clause ]
conflict-clause = ON CONFLICT conflict-algorithm
A CREATE TABLE statement contains 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 consists of the column's name followed by its datatype, then one or more optional column constraints. The datatype for the column doesn't restrict what data may be put in it; see the Datatypes in QDB chapter for further information.
There are no arbitrary limits on the number of columns or on the number of constraints in a table. As well, there is no arbitrary limit on the amount of data in a row.
Tables are removed using the DROP TABLE statement.
If the TEMP or TEMPORARY keyword is used, then the created table is visible only within that same database connection and is automatically deleted when the connection is closed. Any indexes created on a temporary table are also temporary. Temporary tables and indexes are stored in a separate file distinct from the main database file.
If database-name is specified, then the table is created in the named database. It is an error to specify both database-name and the TEMP keyword, unless database-name is temp. If no database name is specified and the TEMP keyword is not present, the table is created in the main database.
The DEFAULT constraint specifies a default value to use for a column when doing an INSERT. The constraint value may be NULL, a string constant, or a number. It may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE, or CURRENT_TIMESTAMP.
If the value is NULL, a string constant, or a number, it is literally inserted into the column whenever an INSERT statement that doesn't specify a value for the column is executed.
If the value is one of the special keywords, then the current UTC date and/or time is inserted into the column. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, it's YYYY-MM-DD. For CURRENT_TIMESTAMP, it's YYYY-MM-DD HH:MM:SS.
Specifying PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if the primary key is on a single column that has the INTEGER datatype, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may hold only unique integer values. (Except for this one case, QDB 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 doesn't have an INTEGER PRIMARY KEY column, then the B-Tree key will be an 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 there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT, which modifies the way that QDB automatically generates B-Tree keys. Additional detail on automatic B-Tree key generation is given in “Row ID and Autoincrement”.
The UNIQUE constraint is similar to the PRIMARY KEY constraint, except that a table may have any number of UNIQUE constraints. Each UNIQUE constraint creates an index on the specified columns. This index must contain unique keys.
The COLLATE constraint specifies which collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default.
A NOT NULL constraint dictates that the associated column may not contain a NULL value. Attempting to set the column value to NULL when inserting or updating a row causes a constraint violation.
CHECK constraints are supported and enforced. Each time a row is inserted or updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL or any other non-zero value, it is not a constraint violation.
Constraint violations are handled by constraint conflict-resolution algorithms. Each PRIMARY KEY, UNIQUE, NOT NULL, and CHECK constraint has a default algorithm for resolving constraint conflicts. The optional conflict-clause following a constraint lets you specify an alternative algorithm for resolving constraint conflicts. The default algorithm is ABORT. Different constraints within the same table may have different conflict-resolution algorithms.
If a 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 ON CONFLICT section for additional information.
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 QDB'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 stored in sqlite_master in place of the original command. The text of CREATE TEMPORARY TABLE statements is stored in the sqlite_temp_master table.
If the optional IF NOT EXISTS clause is present and another table with the same name aleady exists, then this command becomes a no-op.