Documentation Source Text

Check-in [91fa93da86]
Login

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

Overview
Comment:Attempt to clarify the CREATE TABLE documentation. In the generated column documentation, make it clear that SQLite deliberately ignores the collating sequence of the AS expression when determining the collating sequence of the generated column.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 91fa93da8654a8456e515affd8d28c3df3420b0fd3fa74dfbaa4993dc1ce1e4a
User & Date: drh 2020-01-31 20:15:10.487
Context
2020-02-06
23:40
Clarification the UPSERT documentation. (check-in: 271f686636 user: drh tags: branch-3.31)
2020-02-03
16:23
Add change log for 3.32.0. (check-in: 69840d9eb5 user: drh tags: trunk)
2020-01-31
20:15
Attempt to clarify the CREATE TABLE documentation. In the generated column documentation, make it clear that SQLite deliberately ignores the collating sequence of the AS expression when determining the collating sequence of the generated column. (check-in: 91fa93da86 user: drh tags: trunk)
2020-01-29
23:04
Enhancements to the FTS3/4 documentation about the limitations of BOMs in string literals with the simple tokenizer in a UTF16 database. (check-in: 4d11f30804 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/gencol.in.
123
124
125
126
127
128
129






130
131
132
133
134
135
136

<li><p>
^Every table must have at least one non-generated column.

<li><p>
^It is not possible to [ALTER TABLE ADD COLUMN] a STORED column.
^One can add a VIRTUAL column, however.






</ol>

<h1>Compatibility</h1>

<p>Generated column support was added with SQLite version 3.31.0
([dateof:3.31.0]).  If an earlier version of SQLite attempts to read
a database file that contains a generated column in its schema, then







>
>
>
>
>
>







123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

<li><p>
^Every table must have at least one non-generated column.

<li><p>
^It is not possible to [ALTER TABLE ADD COLUMN] a STORED column.
^One can add a VIRTUAL column, however.

<li><p>
The datatype and [collating sequence] of the generated column are determined
only by the datatype and [COLLATE clause] on the column definition.
The datatype and collating sequence of the GENERATED ALWAYS AS expression
have no affect on the datatype and collating sequence of the column itself.
</ol>

<h1>Compatibility</h1>

<p>Generated column support was added with SQLite version 3.31.0
([dateof:3.31.0]).  If an earlier version of SQLite attempts to read
a database file that contains a generated column in its schema, then
Changes to pages/lang.in.
1146
1147
1148
1149
1150
1151
1152
1153
1154




1155

1156
1157
1158
1159
1160
1161
1162

1163
1164
1165
1166
1167
1168
1169
[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. The




[generated column] syntax is supported since SQLite 3.31.0 ([dateof:3.31.0]).


<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.

<tcl>hd_fragment dfltval {default column value} {default value} {DEFAULT clauses}</tcl>

<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 any constant expression enclosed in parentheses. A
default value may also be one of the special case-independent keywords







|
|
>
>
>
>
|
>







>







1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
[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 table data.

<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>

<h4>Column Data Types</h4>

<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.

<tcl>hd_fragment dfltval {default column value} {default value} {DEFAULT clauses}</tcl>
<h4>The DEFAULT clause</h4>
<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 any constant expression enclosed in parentheses. A
default value may also be one of the special case-independent keywords
1187
1188
1189
1190
1191
1192
1193


1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206

1207
1208
1209
1210
1211
1212
1213
1214
  <li><p>^If the default value of a column is CURRENT_TIME, CURRENT_DATE or
    CURRENT_TIMESTAMP, 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>

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>

<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.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an [indexed-column] of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a







>
>




|
|
|
<
|
<
|


>
|







1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208

1209

1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
  <li><p>^If the default value of a column is CURRENT_TIME, CURRENT_DATE or
    CURRENT_TIMESTAMP, 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>

<tcl>hd_fragment collateclause {COLLATE clause} {COLLATE constraint}</tcl>
<h4>The COLLATE clause</h4>
<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].

<h4>The GENERATED ALWAYS AS clause</h4>
<p>A column that includes a GENERATED ALWAY AS clause is a [generated column].
Generated columns are supported beginning with SQLite verison 3.31.0 ([dateof:3.31.0]).

See the [generated column|separate documentation] for details on the capabilities and

limitations of generated columns.

<tcl>hd_fragment primkeyconst {PRIMARY KEY} {PRIMARY KEY constraint}</tcl>
<h4>The PRIMARY KEY</h4>
<p>^Each table in SQLite may have at most one PRIMARY KEY. ^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.
  ^The PRIMARY KEY clause must contain only column names &mdash; the use of 
  expressions in an [indexed-column] of a PRIMARY KEY is not supported.
  ^An error is raised if more than one PRIMARY KEY clause appears in a
1234
1235
1236
1237
1238
1239
1240

1241
1242
1243
1244
1245
1246
1247
1248
  the table is a [WITHOUT ROWID] table or the column is declared NOT NULL,
  SQLite allows NULL values in a PRIMARY KEY column.  SQLite could be fixed to
  conform to the standard, but doing so might break legacy applications.
  Hence, it has been decided to merely document the fact that SQLite
  allowing NULLs in most PRIMARY KEY columns.

<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint} {UNIQUE constraint}</tcl>

<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 contain a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 







>
|







1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
  the table is a [WITHOUT ROWID] table or the column is declared NOT NULL,
  SQLite allows NULL values in a PRIMARY KEY column.  SQLite could be fixed to
  conform to the standard, but doing so might break legacy applications.
  Hence, it has been decided to merely document the fact that SQLite
  allowing NULLs in most PRIMARY KEY columns.

<tcl>hd_fragment uniqueconst {UNIQUE} {unique constraint} {UNIQUE constraint}</tcl>
<h4>UNIQUE constraints</h4>
<p>^A UNIQUE 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 contain a unique combination
  of values in the columns identified by the UNIQUE constraint. 
  ^For the purposes of UNIQUE constraints, NULL values
  are considered distinct from all other values, including other NULLs.
  ^As with PRIMARY KEYs, a UNIQUE [table-constraint] clause must contain
  only column names &mdash; the use of 
1260
1261
1262
1263
1264
1265
1266

1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277

1278
1279
1280
1281
1282
1283


1284
1285
1286
1287
1288
1289
1290
  <li><p>CREATE TABLE t1(a, b UNIQUE);
  <li><p>CREATE TABLE t1(a, b PRIMARY KEY);
  <li><p>CREATE TABLE t1(a, b);<br>
         CREATE UNIQUE INDEX t1b ON t1(b);
  </ol>

<tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl>

<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 and
  cast to a NUMERIC value in the same way as a [CAST expression]. 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.
  ^The expression of a CHECK constraint may not contain a subquery.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>

<p>^A <b>NOT NULL</b> constraint may only be attached to a column definition,
  not specified as a table constraint.  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







>
|










>
|





>
>







1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
  <li><p>CREATE TABLE t1(a, b UNIQUE);
  <li><p>CREATE TABLE t1(a, b PRIMARY KEY);
  <li><p>CREATE TABLE t1(a, b);<br>
         CREATE UNIQUE INDEX t1b ON t1(b);
  </ol>

<tcl>hd_fragment {ckconst} {CHECK} {CHECK constraint} {CHECK constraints}</tcl>
<h4>CHECK constraints</h4>
<p>^(A CHECK 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 and
  cast to a NUMERIC value in the same way as a [CAST expression]. 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.
  ^The expression of a CHECK constraint may not contain a subquery.

<tcl>hd_fragment {notnullconst} {NOT NULL} {NOT NULL constraint}</tcl>
<h4>NOT NULL constraints</h4>
<p>^A NOT NULL constraint may only be attached to a column definition,
  not specified as a table constraint.  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.)^


<h3>Constraint violations</h3>
<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