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: |
91fa93da8654a8456e515affd8d28c3d |
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
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 | [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 | | | > > > > | > > | 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 | <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]. | > > | | | < | < | > | | 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 — 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 | 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> | > | | 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 — the use of |
︙ | ︙ | |||
1260 1261 1262 1263 1264 1265 1266 | <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> | > | > | > > | 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 |
︙ | ︙ |