Documentation Source Text

Check-in [b80a69c002]
Login

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

Overview
Comment:Updates to the SQL language documentation - mostly to fix requirements marks.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b80a69c0021e344433013bddfdf4f3db7ac48de8
User & Date: drh 2009-12-13 22:20:51
Context
2009-12-13
23:03
Additional cleanup in the SQL language documentation. check-in: d508b9e21c user: drh tags: trunk
22:20
Updates to the SQL language documentation - mostly to fix requirements marks. check-in: b80a69c002 user: drh tags: trunk
2009-12-12
23:58
Continuing improvements to the SQL language documentation. check-in: f9931b6e49 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
...
317
318
319
320
321
322
323
324
325
326
327
328
329

330
331
332
333
334
335
336
337
...
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
...
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
...
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
....
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
....
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393

1394
1395
1396
1397
1398
1399
1400
1401
....
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
....
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
....
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
....
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
....
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
to the renamed table by its new name.

<p> ^The ADD COLUMN syntax
is used to add a new column to an existing table.
^The new column is always appended to the end of the list of existing columns.
The [column-def] rule defines the characteristics of the new column.
^(The new column may take any of the forms permissable in a [CREATE TABLE]
statement, with the following restrictions:
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
</ul>)^

<p>^Note also that when adding a CHECK constraint, the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

................................................................................
write operation creates a [RESERVED] lock.   ^Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
^If the transaction is immediate, then [RESERVED] locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  ^After a BEGIN IMMEDIATE, you are guaranteed that
no other thread or process will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  ^Other processes can continue
to read from the database, however.  ^An exclusive transaction causes
[EXCLUSIVE] locks to be acquired on all databases.  ^After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will

be able to read or write the database until the transaction is
complete.
</p>

<p>
^(An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.  A statement finishes when its
................................................................................
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
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,
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
................................................................................
<p>^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
<a href="autoinc.html">separately</a>.</p>

<p>^The special behavior of INTEGER PRIMARY KEY
is only available if the type name is exactly "INTEGER" (in any mixture
of upper and lower case.)  ^Other integer type names
like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER"
causes the primary key column to behave as an ordinary table column with
integer [affinity] and a unique index, not as an alias for the rowid.
^(The special behavior of INTEGER PRIMARY KEY is only available if the
primary key is a single column.  Multi-column primary keys do not become
aliases for the rowid.)^
^The AUTOINCREMENT keyword only works on a column that is an alias
................................................................................
the [sqlite3_set_authorizer()] interface.  ^If an authorizer callback
returns [SQLITE_IGNORE] for an [SQLITE_DELETE] action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

<h3>Use Of LIMIT</h3>

<p>^If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>

<p>^The optional LIMIT clause can be used to limit the number of
rows deleted, and thereby limit the size of the transaction.
^The ORDER BY clause on a DELETE statement
is used only to determine which rows fall
................................................................................

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^(Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).)^  (A bug: ^SQLite only
understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is by default case sensitive for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)</p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol, underscore or itself matches a

literal percent symbol, underscore or escape character in the string,
respectively.

<p>^The infix LIKE operator is implemented by calling the
application-defined SQL functions [like(<i>Y</i>,<i>X</i>)] or
[like(<i>Y</i>,<i>X</i>,<i>Z</i>)]</a>.</p>

<p>^The LIKE operator can be made case sensitive using the
................................................................................
^The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  ^WHEN expressions are evaluated from left to
right until one is found that is true and the corresponding THEN term
becomes the result.  ^If no WHEN expression is true then the ELSE 
clause determines the result or the result is NULL if there is no ELSE clase.
</p>

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is the "base" expression.  ^There are two basic forms
of a CASE expression: those with and without a base expression.
^In a CASE without a base expression, each WHEN expression is evaluted
................................................................................
[http://en.wikipedia.org/wiki/Julian_day | Julian day number]
expressed as a floating point value.
</p>

<h3>Modifiers</h3>

<p>^The time string can be followed by zero or more modifiers that 
alter the date and time string. ^ Each modifier
is a transformation that is applied to the time string to its left.
^Modifiers are applied from left to right; order is important.
^(The available modifiers are as follows.</p>

<ol>
<li> NNN days
<li> NNN hours
<li> NNN minutes
................................................................................
<li> weekday N
<li> unixepoch
<li> localtime
<li> utc 
</ol>)^

<p>^The first six modifiers (1 through 6) 
simply add the specified amount of time to the date 
specified by the preceding timestring.
^Note that "&plusmn;NNN months" works by rendering the original date into
the YYYY-MM-DD format, adding the &plusmn;NNN to the MM month value, then
normalizing the result.  ^Thus, for example, the data 2001-03-31 modified
by '+1 month' initially yields 2001-04-31, but April only has 30 days
so the date is normalized to 2001-05-01.  ^A similar effect occurs when
the original date is February 29 of a leapyear and the modifier is
&plusmn;N years where N is not a multiple of four.</p>
................................................................................
}

funcdef {min(X)} {*minAggFunc *agg_min {min() aggregate function}} {
  ^The min() aggregate function
  returns the minimum non-NULL value of all values in the group.
  ^The minimum value is the first non-NULL value that would appear
  in an ORDER BY of the column.
  ^NULL is only returned if and only if there are no non-NULL values in the 
  group.
}

funcdef {sum(X) total(X)} {
  *sumFunc *sum *total
  {sum() aggregate function}
  {total() aggregate function}
................................................................................
default value is specified.
</p>

<p>^The second form of the INSERT statement takes its data from a
SELECT statement.  ^The number of columns in the result of the
SELECT must exactly match the number of columns in the table if
no column list is specified, or it must match the number of columns
name in the column list.  ^A new entry is made in the table
for every row of the SELECT result.  ^The SELECT may be simple
or compound.</p>

<p>^The third form of an INSERT statement is with DEFAULT VALUES.
^The INSERT ... DEFAULT VALUES statement simply creates a single new
row in the table in which each column is filled with its default value.</p>

<p>^The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled
[ON CONFLICT] for additional information.
^For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 







|

|
|

|




|







 







|
|



|
>
|







 







|

|







 







|







 







|
|







 







|

|







 







|


|





|
>
|







 







|
|







 







|
|







 







|
|







 







|







 







|




|
|







150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
...
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
...
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
...
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
....
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
....
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
....
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
....
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
....
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
....
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
....
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
to the renamed table by its new name.

<p> ^The ADD COLUMN syntax
is used to add a new column to an existing table.
^The new column is always appended to the end of the list of existing columns.
The [column-def] rule defines the characteristics of the new column.
^(The new column may take any of the forms permissable in a [CREATE TABLE]
statement, with the following restrictions:)^
<ul>
<li>^The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>^The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
</ul>

<p>^Note also that when adding a CHECK constraint, the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

................................................................................
write operation creates a [RESERVED] lock.   ^Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
^If the transaction is immediate, then [RESERVED] locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  ^After a BEGIN IMMEDIATE, 
no other [database connection] will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  ^Other processes can continue
to read from the database, however.  ^An exclusive transaction causes
[EXCLUSIVE] locks to be acquired on all databases.  ^After a BEGIN
EXCLUSIVE, no other [database connection] except for [read_uncommitted]
connections will be able to read the database and no other connection without
exception will be able to write the database until the transaction is
complete.
</p>

<p>
^(An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.  A statement finishes when its
................................................................................
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^(The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).)^</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
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
................................................................................
<p>^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
<a href="autoinc.html">separately</a>.</p>

<p>^The special behavior of INTEGER PRIMARY KEY
is only available if the type name is exactly "INTEGER" in any mixture
of upper and lower case.  ^Other integer type names
like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER"
causes the primary key column to behave as an ordinary table column with
integer [affinity] and a unique index, not as an alias for the rowid.
^(The special behavior of INTEGER PRIMARY KEY is only available if the
primary key is a single column.  Multi-column primary keys do not become
aliases for the rowid.)^
^The AUTOINCREMENT keyword only works on a column that is an alias
................................................................................
the [sqlite3_set_authorizer()] interface.  ^If an authorizer callback
returns [SQLITE_IGNORE] for an [SQLITE_DELETE] action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

<h3>Use Of LIMIT</h3>

<p>^(If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:)^</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>

<p>^The optional LIMIT clause can be used to limit the number of
rows deleted, and thereby limit the size of the transaction.
^The ORDER BY clause on a DELETE statement
is used only to determine which rows fall
................................................................................

<tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any
sequence of zero or more characters in the string.  ^An underscore
(\"_\") in the LIKE pattern matches any single character in the
string.  ^(Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).)^  (A bug: ^SQLite only
understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is case sensitive by default for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)<p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol (%), underscore (_), or a second
instance of the escape character itself matches a
literal percent symbol, underscore, or a single escape character,
respectively.

<p>^The infix LIKE operator is implemented by calling the
application-defined SQL functions [like(<i>Y</i>,<i>X</i>)] or
[like(<i>Y</i>,<i>X</i>,<i>Z</i>)]</a>.</p>

<p>^The LIKE operator can be made case sensitive using the
................................................................................
^The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  ^WHEN expressions are evaluated from left to
right until one is found that is true, at which point the corresponding
THEN term becomes the result.  ^If no WHEN expression is true then the ELSE 
clause determines the result or the result is NULL if there is no ELSE clase.
</p>

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is the "base" expression.  ^There are two basic forms
of a CASE expression: those with and without a base expression.
^In a CASE without a base expression, each WHEN expression is evaluted
................................................................................
[http://en.wikipedia.org/wiki/Julian_day | Julian day number]
expressed as a floating point value.
</p>

<h3>Modifiers</h3>

<p>^The time string can be followed by zero or more modifiers that 
alter the date and time string. ^Each modifier
is a transformation that is applied to the time value to its left.
^Modifiers are applied from left to right; order is important.
^(The available modifiers are as follows.</p>

<ol>
<li> NNN days
<li> NNN hours
<li> NNN minutes
................................................................................
<li> weekday N
<li> unixepoch
<li> localtime
<li> utc 
</ol>)^

<p>^The first six modifiers (1 through 6) 
simply add the specified amount of time to the date and time 
specified by the preceding timestring and modifiers.
^Note that "&plusmn;NNN months" works by rendering the original date into
the YYYY-MM-DD format, adding the &plusmn;NNN to the MM month value, then
normalizing the result.  ^Thus, for example, the data 2001-03-31 modified
by '+1 month' initially yields 2001-04-31, but April only has 30 days
so the date is normalized to 2001-05-01.  ^A similar effect occurs when
the original date is February 29 of a leapyear and the modifier is
&plusmn;N years where N is not a multiple of four.</p>
................................................................................
}

funcdef {min(X)} {*minAggFunc *agg_min {min() aggregate function}} {
  ^The min() aggregate function
  returns the minimum non-NULL value of all values in the group.
  ^The minimum value is the first non-NULL value that would appear
  in an ORDER BY of the column.
  ^NULL is returned if and only if there are no non-NULL values in the 
  group.
}

funcdef {sum(X) total(X)} {
  *sumFunc *sum *total
  {sum() aggregate function}
  {total() aggregate function}
................................................................................
default value is specified.
</p>

<p>^The second form of the INSERT statement takes its data from a
SELECT statement.  ^The number of columns in the result of the
SELECT must exactly match the number of columns in the table if
no column list is specified, or it must match the number of columns
named in the column list.  ^A new entry is made in the table
for every row of the SELECT result.  ^The SELECT may be simple
or compound.</p>

<p>^The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement simply creates a single new
row in the table in which each column is filled with its default value.)^</p>

<p>^The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one INSERT command.
See the section titled
[ON CONFLICT] for additional information.
^For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an