Documentation Source Text

Check-in [2092d92251]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Changes to the CREATE TABLE documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2092d92251e20a2bb4cbac352267fa83086bc9ee
User & Date: dan 2010-09-28 17:38:49
Context
2010-09-29
01:38
Various documentation typo fixes and updates. check-in: 161f14d929 user: drh tags: trunk
2010-09-28
17:38
Changes to the CREATE TABLE documentation. check-in: 2092d92251 user: dan tags: trunk
2010-09-24
19:00
Update the REINDEX syntax diagram to allow for no arguments. check-in: f55ccb05aa user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718


















































































































719
720
721
722
723
724
725
....
2928
2929
2930
2931
2932
2933
2934

2935
2936
2937
2938
2939
2940
2941
BubbleDiagram column-def
BubbleDiagram type-name
BubbleDiagram column-constraint
BubbleDiagram table-constraint
BubbleDiagram foreign-key-clause
</tcl>

<p>^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 "<b>sqlite_</b>" are reserved
for use by SQLite itself and cannot normally
appear in a CREATE TABLE statement.</p>

<tcl>hd_fragment {tablecoldef} {column definition} {column definitions}</tcl>
<p>^Each column definition is the name of the column optionally followed by the
[datatype] for that column, then one or more optional column constraints.
^SQLite uses [dynamic typing]; 
the datatype for the column does not restrict what data may be put
in that column.
^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.
<p>
^The DEFAULT constraint specifies a default value to use when doing an [INSERT].
^The value may be NULL, a string constant, a number, or a constant expression
enclosed in parentheses.
^The default value 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 number, it is 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".
</p>

<p>^(The PRIMARY KEY attribute normally creates a UNIQUE index on
the column or columns that are specified as the PRIMARY KEY.  The only
exception to this behavior is special [INTEGER PRIMARY KEY] column,
described below.)^
^(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.</p>

<p>^SQLite uses [dynamic typing] instead of static typing.  ^Except for the
special case of [INTEGER PRIMARY KEY], SQLite will allow values of any
type to be stored in any column regardless of the declared datatype of
that column.  ^The declared datatype is a [affinity | type affinity] that
SQLite attempts to comply with, but the operation will proceed even if
compliance is not possible.</p>

<p>^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.</p>

<p> ^If a &lt;database-name&gt; is specified, then the table is created in 
the named database. ^It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". ^If no
database name is specified and the TEMP keyword is not present then
the table is created in the main database.</p>

<p>^The optional [conflict clause] following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
^If no conflict clause is specified, the ABORT algorithm is used.
^Different constraints within the same
table may have different conflict resolution algorithms.
^If an [INSERT] or [UPDATE] statement specifies a conflict
resolution algorithm, then the algorithm specified on the INSERT or
UPDATE statement overrides the algorithm specified in the 
CREATE TABLE statement.
See the section titled
[ON CONFLICT] for additional information.</p>

<p>CHECK constraints are supported as of [version 3.3.0].  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>^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.</p>


<p>^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.</p>

<p>^If the optional IF NOT EXISTS clause is present and another table
with the same name already exists, then this command becomes a no-op.</p>

<p>^Tables are removed using the [DROP TABLE] statement.  </p>



















































































































<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Every row of every SQLite table has a 64-bit signed integer key 
that is unique within the same table.
This integer is usually called the "rowid".  ^The rowid is the actual key used
in the B-Tree that implements an SQLite table.  ^Rows are stored in
................................................................................

<p>^(When three or more simple SELECTs are connected into a compound SELECT,
they group from left to right. In other words, if "A", "B" and "C" are all
simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^

</p>


<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

<p>If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is undefined.
Or, if a SELECT statement does have an ORDER BY clause, then the list of
expressions attached to the ORDER BY determine the order in which rows
are returned to the user. ^Rows are first sorted based on the results of







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
....
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
BubbleDiagram column-def
BubbleDiagram type-name
BubbleDiagram column-constraint
BubbleDiagram table-constraint
BubbleDiagram foreign-key-clause
</tcl>

<p>The "CREATE TABLE" command is used to create a new table in an SQLite 
database. A CREATE TABLE command specifies the following attributes of the
new table:

<ul>
  <li><p>The name of the new table.

  <li><p> The database in which the new table is created. Tables may be 
      created in the main database, the temp database, or in any attached
      database.

  <li><p> The name of each column in the table.

  <li><p> The declared type of each column in the table.

  <li><p> A default value or expression for each column in the table.

  <li><p> A default collation sequence to use with each column.

  <li><p> Optionally, a PRIMARY KEY for the table. Both single column and
       composite (multiple column) primary keys are supported.

  <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
       NULL, CHECK and FOREIGN KEY constraints.
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with
  "sqlite_".)^

<p> ^If a &lt;database-name&gt; is specified, it must be either "main", 
  "temp", or the name of an [ATTACH DATABASE|attached database]. ^In this case
  the new table is created in the named database. ^If the "TEMP" or "TEMPORARY"
  keyword occurs between the "CREATE" and "TABLE" then the new table is
  created in the temp database. ^It is an error to specify both a 
  &lt;database-name&gt; and the TEMP or TEMPORARY keyword, unless the
  &lt;database-name&gt; is "temp". ^If no database name is specified and the
  TEMP keyword is not present then the table is created in the main
  database.

<p>
  ^It is usually an error to attempt to create a new table in a database that
  already contains a table, index or view of the same name. ^However, if the
  "IF NOT EXISTS" clause is specified as part of the CREATE TABLE statement and
  a table or view of the same name already exists, the CREATE TABLE command
  simply has no effect (and no error message is returned). ^An error is still
  returned if the table cannot be created because of an existing index, even 
  if the "IF NOT EXISTS" clause is specified.

<p>^It is not an error to create a table that has the same name as an 
  existing [CREATE TRIGGER|trigger].

<p>^Tables are removed using the [DROP TABLE] statement.  </p>

<h3>CREATE TABLE AS Statements</h3>

<p>^A "CREATE TABLE AS" statement creates and populates a database table based
on the results of a SELECT statement. ^(The table has the same number of 
columns as the rows returned by the SELECT statement. The name of each column 
is the same as the name of the corresponding column in the result set of the 
SELECT statement.)^ ^(The declared type of each column is determined by the
[expression affinity] of the corresponding expression in the result set of the
SELECT statement, as follows:
</p>

<center><table border=1>
  <tr><th>Expression Affinity   <th>Column Declared Type
  <tr><td>TEXT                  <td>"TEXT"
  <tr><td>NUMERIC               <td>"NUM"
  <tr><td>INTEGER               <td>"INT"
  <tr><td>REAL                  <td>"REAL"
  <tr><td>NONE                  <td>"" (empty string)
</table></center>)^

<p>^(A table created using CREATE TABLE AS has no PRIMARY KEY and no
constraints of any kind. The default value of each column is NULL. The default
collation sequence for each column of the new table is BINARY.)^

<p>^Tables created using CREATE TABLE AS are initially populated with the
rows of data returned by the SELECT statement. ^Rows are assigned contiguously
ascending [rowid] values, starting with 1, in the [order by|order] that they
are returned by the SELECT statement.

<tcl>hd_fragment {tablecoldef} {column definition} {column definitions}</tcl>
<h3>Column Definitions</h3>

<p>Unless it is a CREATE TABLE AS statement, a CREATE TABLE includes one or
more [column-def|column definitions], optionally followed by a list of 
[table-constraint|table constraints].  Each column definition consists of the
name of the column, optionally followed by the declared type of the column,
then one or more optional [column-constraint|column constraints]. Included in
the definition of "column constraints" for the purposes of the previous
statement are the COLLATE and DEFAULT clauses, even though these are not really
constraints in the sense that they do not restrict the data that the table may
contain. The other constraints - NOT NULL, CHECK, UNIQUE, PRIMARY KEY and
FOREIGN KEY constraints - impose restrictions on the tables data, and are are
described under [constraints|SQL Data Constraints] below.

<p>^Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses [dynamic typing]. ^The declared type of a column is used to
determine the [affinity] of the column only.

<p>The DEFAULT clause specifies a default value to use for the column if no
value is explicitly provided by the user when doing an [INSERT]. ^If there
is no explicit DEFAULT clause attached to a column definition, then the 
default value of the column is NULL. ^(An explicit DEFAULT clause may specify
that the default value is NULL, a string constant, a blob constant, a
signed-number, or an arbitrary expression enclosed in parentheses. An explicit
default value may also be one of the special case-independent keywords
CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.)^

<p>^(Each time a row is inserted into the table by an INSERT statement that 
does not provide explicit values for all table columns the values stored in
the new row are determined by their default values)^, as follows:

<ul>
  <li><p>^If the default value of the column is a constant NULL, text, blob or
    signed-number value, then that value is used directly in the new row.

  <li><p>^If the default value of a column is an expression in parentheses, then
    the expression is evaluated once for each row inserted and the results
    used in the new row.

  <li><p>^If the default value of a column is CURRENT_TIME, CURRENT_DATE or
    CURRENT_DATETIME, then the value used in the new row is a text
    representation of the current UTC date and/or time. ^For CURRENT_TIME, the
    format of the value is "HH:MM:SS". ^For CURRENT_DATE, "YYYY-MM-DD". ^The
    format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</ul>

<p>^The COLLATE clause specifies the name of a [collating sequence] to use as
the default collation sequence for the column. ^If no COLLATE clause is
specified, the default collation sequence is [BINARY].

<p>^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.</p>

<tcl>hd_fragment {constraints} {constraints}</tcl>
<h3>SQL Data Constraints</h3>

<p>^Each table in SQLite may have at most one <b>PRIMARY KEY</b>. ^If the
  keywords PRIMARY KEY are added to a column definition, then the primary key
  for the table consists of that single column. ^Or, if a PRIMARY KEY clause 
  is specified as a [table-constraint], then the primary key of the table
  consists of the list of columns specified as part of the PRIMARY KEY clause.
  ^If there is more than one PRIMARY KEY clause in a single CREATE TABLE
  statement, it is an error.

<p>If a table has a single column primary key, and the declared type of that
  column is "INTEGER", then the column is known as an [INTEGER PRIMARY KEY].
  See below for a description of the special properties and behaviours
  associated with an [INTEGER PRIMARY KEY].

<p>^Each row in a table with a primary key must feature a unique combination
  of values in its primary key columns. ^For the purposes of the previous
  statement, NULL values are considered distinct from all other values,
  including other NULLs. ^If an [INSERT] or [UPDATE] statement attempts to
  modify the table content so that two or more rows feature identical primary
  key values, it is a constraint violation. According to the SQL standard,
  PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a
  long-standing coding oversight, this is not the case in SQLite. ^Unless the
  column is an [INTEGER PRIMARY KEY] 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.

<p>^A <b>UNIQUE</b> constraint is similar to a PRIMARY KEY constraint, except
  that a single table may have any number of UNIQUE constraints. ^For each
  UNIQUE constraint on the table, each row must feature a unique combination
  of values in the columns identified by the UNIQUE constraint. ^As with
  PRIMARY KEY constraints, for the purposes of UNIQUE constraints NULL values
  are considered distinct from all other values (including other NULLs).
  ^If an [INSERT] or [UPDATE] statement attempts to modify the table content so
  that two or more rows feature identical values in a set of columns that
  are subject to a UNIQUE constraint, it is a constraint violation.

<p>^[INTEGER PRIMARY KEY] columns aside, both UNIQUE and PRIMARY KEY
  constraints are implemented by creating an index in the database (in the same
  way as a [CREATE INDEX|"CREATE UNIQUE INDEX"] statement would). ^This index
  is used like any other index in the database to [optimizer|optimize queries].
  As a result, there often no advantage (but significant overhead) in creating
  an index on a set of columns that are already collectively subject to a
  UNIQUE or PRIMARY KEY constraint.

<p>^(A <b>CHECK</b> constraint may be attached to a column definition or
  specified as a table constraint. In practice it makes no difference.)^ ^(Each
  time a new row is inserted into the table or an existing row is updated,
  the expression associated with each CHECK constraint is evaluated as a
  [boolean expression]. If the result is false, then a constraint violation
  has occurred.)^

<p>CHECK constraints have been supported since [version 3.3.0]. Prior to
  version 3.3.0, CHECK constraints were parsed but not enforced.

<p>^A <b>NOT NULL</b> constraint may only be attached to a single column.
  Not surprisingly, ^(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 a new row or updating an existing one causes a constraint
  violation.)^

<p>Exactly how a constraint violation is dealt with is determined by the
  [conflict clause|constraint conflict resolution algorithm]. Each 
  PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraint has a default conflict
  resolution algorithm. ^PRIMARY KEY, UNIQUE and NOT NULL constraints may be
  explicitly assigned a default conflict resolution algorithm by including
  a [conflict-clause] in their definitions. ^Or, if a constraint definition
  does not include a [conflict-clause] or it is a CHECK constraint, the default
  conflict resolution algorithm is ABORT. ^Different constraints within the
  same table may have different default conflict resolution algorithms. See
  the section titled [ON CONFLICT] for additional information.

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
<h3>ROWIDs and the INTEGER PRIMARY KEY</h3>

<p>^Every row of every SQLite table has a 64-bit signed integer key 
that is unique within the same table.
This integer is usually called the "rowid".  ^The rowid is the actual key used
in the B-Tree that implements an SQLite table.  ^Rows are stored in
................................................................................

<p>^(When three or more simple SELECTs are connected into a compound SELECT,
they group from left to right. In other words, if "A", "B" and "C" are all
simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^

</p>

<tcl>hd_fragment orderby {order by}</tcl>
<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

<p>If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is undefined.
Or, if a SELECT statement does have an ORDER BY clause, then the list of
expressions attached to the ORDER BY determine the order in which rows
are returned to the user. ^Rows are first sorted based on the results of