SQLite Query Language: CREATE TABLE body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%; } a { color: #45735f } a:visited { color: #734559 } .logo { position:absolute; margin:3px; } .tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px; } .toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px; } .toolbar a { color: white; text-decoration: none; padding: 6px 12px; } .toolbar a:visited { color: white; } .toolbar a:hover { color: #80a796; background: white; } .content { margin: 5%; } .content dt { font-weight:bold; } .content dd { margin-bottom: 25px; margin-left:20%; } .content ul { padding:0px; padding-left: 15px; margin:0px; } /* rounded corners */ .se { background: url(images/se.png) 100% 100% no-repeat #80a796} .sw { background: url(images/sw.png) 0% 100% no-repeat } .ne { background: url(images/ne.png) 100% 0% no-repeat } .nw { background: url(images/nw.png) 0% 0% no-repeat } border="0"> Small. Fast. Reliable.Choose any three. About Sitemap Documentation Download License News Developers Support SQL As Understood By SQLite CREATE TABLE sql-command ::= CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name .] table-name ( column-def [, column-def]* [, constraint]* ) sql-command ::= 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 ::= ON CONFLICT conflict-algorithm 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. 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. The datatype for the column does not restrict what data may be put in that column. See Datatypes In SQLite Version 3 for additional information. The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple entries with the value of NULL. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default. The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant or a number. Starting with version 3.1.0, the default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is literally inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS". Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. 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. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately. According to the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly. If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible within that same database connection and is automatically deleted when the database connection 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. If a <database-name> is specified, then the table is created in the named database. It is an error to specify both a <database-name> and the TEMP keyword, unless the <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 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 INSERT or UPDATE statement 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 supported as of version 3.3.0. Prior to version 3.3.0, CHECK constraints were parsed but not enforced. The number of columns in a table is limited by the SQLITE_MAX_COLUMN compile-time parameter. A single row of a table cannot store more than SQLITE_MAX_LENGTH bytes of data. Both of these limits can be lowered at runtime using the sqlite3_limit() C/C++ interface. 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 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 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. 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. Tables are removed using the DROP TABLE statement. This page last modified 2008/04/27 17:48:52 UTC
