Choose any three.
SQLite Requirement Matrix Details
lang_createtable.html
Index Summary Markup Original
R-17899-04554-16139-08865-17180-48146-22421-25181 tcl slt th3 src
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_".
/* IMP: R-17899-04554 */
# EVIDENCE-OF: R-17899-04554 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_".
R-18448-33677-24948-48953-25784-65474-15600-00219 tcl slt th3 src
If a schema-name is specified, it must be either "main", "temp", or the name of an attached database.
/* IMP: R-18448-33677 */
# EVIDENCE-OF: R-18448-33677 If a schema-name is specified, it must be # either "main", "temp", or the name of an attached database.
R-39822-07822-55219-05739-15484-26699-12744-24448 tcl slt th3 src
In this case the new table is created in the named database.
/* IMP: R-39822-07822 */
# EVIDENCE-OF: R-39822-07822 In this case the new table is created in # the named database.
R-18895-27365-20289-12607-60428-18477-59440-44133 tcl slt th3 src
If the "TEMP" or "TEMPORARY" keyword occurs between the "CREATE" and "TABLE" then the new table is created in the temp database.
/* IMP: R-18895-27365 */
# EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs # between the "CREATE" and "TABLE" then the new table is created in the # temp database.
R-23976-43329-40116-37521-45580-17979-29083-00221 tcl slt th3 src
It is an error to specify both a schema-name and the TEMP or TEMPORARY keyword, unless the schema-name is "temp".
/* IMP: R-23976-43329 */
# EVIDENCE-OF: R-23976-43329 It is an error to specify both a # schema-name and the TEMP or TEMPORARY keyword, unless the schema-name # is "temp".
R-31997-24564-21710-03958-33842-06684-32775-28605 tcl slt th3 src
If no schema name is specified and the TEMP keyword is not present then the table is created in the main database.
/* IMP: R-31997-24564 */
# EVIDENCE-OF: R-31997-24564 If no schema name is specified and the TEMP # keyword is not present then the table is created in the main database.
R-01232-54838-06099-51737-48729-53565-23196-29255 tcl slt th3 src
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.
/* IMP: R-01232-54838 */
# EVIDENCE-OF: R-01232-54838 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.
R-33917-24086-34361-61973-51178-03721-35954-00662 tcl slt th3 src
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).
/* IMP: R-33917-24086 */
# EVIDENCE-OF: R-33917-24086 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).
R-16465-40078-63621-42444-05703-20205-45309-57719 tcl slt th3 src
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.
/* IMP: R-16465-40078 */
# EVIDENCE-OF: R-16465-40078 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.
R-05513-33819-59240-57490-36750-56355-20133-11323 tcl slt th3 src
It is not an error to create a table that has the same name as an existing trigger.
/* IMP: R-05513-33819 */
# EVIDENCE-OF: R-05513-33819 It is not an error to create a table that # has the same name as an existing trigger.
R-22283-14179-18327-11516-19708-03741-18136-56509 tcl slt th3 src
Tables are removed using the DROP TABLE statement.
/* IMP: R-22283-14179 */
# EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE # statement.
R-32365-09043-50030-24839-64719-45976-15708-15010 tcl slt th3 src
A "CREATE TABLE ... AS SELECT" statement creates and populates a database table based on the results of a SELECT statement.
/* IMP: R-32365-09043 */
# EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement # creates and populates a database table based on the results of a # SELECT statement.
R-64828-59568-61067-60898-36143-56429-01681-45331 tcl slt th3 src
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.
/* IMP: R-64828-59568 */
# EVIDENCE-OF: R-64828-59568 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.
R-55407-45319-53851-58560-05892-08992-42854-39100 tcl slt th3 src
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:
| Expression Affinity | Column Declared Type |
|---|---|
| TEXT | "TEXT" |
| NUMERIC | "NUM" |
| INTEGER | "INT" |
| REAL | "REAL" |
| BLOB (a.k.a "NONE") | "" (empty string) |
/* IMP: R-55407-45319 */
# EVIDENCE-OF: R-55407-45319 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: Expression # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT" # REAL "REAL" BLOB (a.k.a "NONE") "" (empty string)
R-16667-09772-49719-31383-20699-54843-21151-14987 tcl slt th3 src
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.
/* IMP: R-16667-09772 */
# EVIDENCE-OF: R-16667-09772 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.
R-24153-28352-05918-29721-55341-26989-15412-17405 tcl slt th3 src
Tables created using CREATE TABLE AS are initially populated with the rows of data returned by the SELECT statement.
/* IMP: R-24153-28352 */
# EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are # initially populated with the rows of data returned by the SELECT # statement.
R-08224-30249-18275-36906-02216-43610-40508-55733 tcl slt th3 src
Rows are assigned contiguously ascending rowid values, starting with 1, in the order that they are returned by the SELECT statement.
/* IMP: R-08224-30249 */
# EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending # rowid values, starting with 1, in the order that they are returned by # the SELECT statement.
R-25473-20557-44472-04975-51987-58936-13072-57785 tcl slt th3 src
The number of columns in a table is limited by the SQLITE_MAX_COLUMN compile-time parameter.
/* IMP: R-25473-20557 */
# EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited # by the SQLITE_MAX_COLUMN compile-time parameter.
R-55351-62604-52448-54183-29592-46679-39320-37048 tcl slt th3 src
A single row of a table cannot store more than SQLITE_MAX_LENGTH bytes of data.
/* IMP: R-55351-62604 */
# EVIDENCE-OF: R-55351-62604 A single row of a table cannot store more # than SQLITE_MAX_LENGTH bytes of data.
R-27775-64721-40407-04494-31964-21217-54345-17508 tcl slt th3 src
Both of these limits can be lowered at runtime using the sqlite3_limit() C/C++ interface.
/* IMP: R-27775-64721 */
# EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at # runtime using the sqlite3_limit() C/C++ interface.
R-27219-49057-63143-35616-48690-21583-47587-41101 tcl slt th3 src
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.
/* IMP: R-27219-49057 */
# EVIDENCE-OF: R-27219-49057 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.
R-10565-09557-60911-42161-01648-20022-48231-49571 tcl slt th3 src
The declared type of a column is used to determine the affinity of the column only.
/* IMP: R-10565-09557 */
# EVIDENCE-OF: R-10565-09557 The declared type of a column is used to # determine the affinity of the column only.
R-42316-09582-63519-16650-16131-27933-31807-16513 tcl slt th3 src
If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.
/* IMP: R-42316-09582 */
# EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause # attached to a column definition, then the default value of the column # is NULL.
R-07343-35026-05102-53943-15495-27224-50604-38447 tcl slt th3 src
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 CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
/* IMP: R-07343-35026 */
# EVIDENCE-OF: R-07343-35026 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 # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
R-33440-07331-05623-12245-41589-49905-51548-00120 tcl slt th3 src
For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes.
/* IMP: R-33440-07331 */
# EVIDENCE-OF: R-33440-07331 For the purposes of the DEFAULT clause, an # expression is considered constant if it contains no sub-queries, # column or table references, bound parameters, or string literals # enclosed in double-quotes instead of single-quotes.
R-18814-23501-09117-45657-61449-22992-54979-37312 tcl slt th3 src
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
/* IMP: R-18814-23501 */
# EVIDENCE-OF: R-18814-23501 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
R-12572-62501-53672-53219-61722-28742-24509-13424 tcl slt th3 src
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.
/* IMP: R-12572-62501 */
# EVIDENCE-OF: R-12572-62501 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.
R-60616-50251-50131-13271-23159-13048-15084-43797 tcl slt th3 src
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.
/* IMP: R-60616-50251 */
# EVIDENCE-OF: R-60616-50251 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.
R-15363-55230-31292-18565-56039-36923-37219-31589 tcl slt th3 src
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.
/* IMP: R-15363-55230 */
# EVIDENCE-OF: R-15363-55230 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.
R-62327-53843-00789-05763-27259-51867-41833-27488 tcl slt th3 src
For CURRENT_TIME, the format of the value is "HH:MM:SS".
/* IMP: R-62327-53843 */
# EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value # is "HH:MM:SS".
R-03775-43471-64071-42182-04548-19220-35511-30117 tcl slt th3 src
For CURRENT_DATE, "YYYY-MM-DD".
/* IMP: R-03775-43471 */
# EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
R-07677-44926-50918-49822-13370-30168-54851-13094 tcl slt th3 src
The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
/* IMP: R-07677-44926 */
# EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is # "YYYY-MM-DD HH:MM:SS".
R-55061-47754-54391-02401-15466-20656-29553-32154 tcl slt th3 src
The COLLATE clause specifies the name of a collating sequence to use as the default collation sequence for the column.
/* IMP: R-55061-47754 */
# EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a # collating sequence to use as the default collation sequence for the # column.
R-40275-54363-02570-13891-25879-32462-51713-20144 tcl slt th3 src
If no COLLATE clause is specified, the default collation sequence is BINARY.
/* IMP: R-40275-54363 */
# EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the # default collation sequence is BINARY.
R-52382-54248-22062-55622-65270-38332-60427-10536 tcl slt th3 src
Each table in SQLite may have at most one PRIMARY KEY.
/* IMP: R-52382-54248 */
# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one # PRIMARY KEY.
R-41411-18837-33884-40601-61407-45364-54319-00196 tcl slt th3 src
If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column.
/* IMP: R-41411-18837 */
# EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a # column definition, then the primary key for the table consists of that # single column.
R-31775-48204-44006-26750-02620-57678-37164-54735 tcl slt th3 src
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.
/* IMP: R-31775-48204 */
# EVIDENCE-OF: R-31775-48204 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.
R-59051-35706-17689-00291-19799-28494-42695-25135 tcl slt th3 src
The PRIMARY KEY clause must contain only column names — the use of expressions in an indexed-column of a PRIMARY KEY is not supported.
/* IMP: R-59051-35706 */
# EVIDENCE-OF: R-59051-35706 The PRIMARY KEY clause must contain only # column names — the use of expressions in an indexed-column # of a PRIMARY KEY is not supported.
R-31826-01813-36062-43919-63231-47318-45635-11887 tcl slt th3 src
An error is raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement.
/* IMP: R-31826-01813 */
# EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY # KEY clause appears in a CREATE TABLE statement.
R-54755-39291-22328-03349-12952-06333-56270-04082 tcl slt th3 src
The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables.
/* IMP: R-54755-39291 */
# EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary # tables but is required for WITHOUT ROWID tables.
R-59124-61339-46915-41859-25282-43163-13207-41482 tcl slt th3 src
Each row in a table with a primary key must have a unique combination of values in its primary key columns.
tcl/e_createtable.test:1183 tcl/e_createtable.test:1333
/* IMP: R-59124-61339 */
# EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must # have a unique combination of values in its primary key columns.
R-52572-02078-29727-01284-61421-00978-11983-52043 tcl slt th3 src
For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs.
/* IMP: R-52572-02078 */
# EVIDENCE-OF: R-52572-02078 For the purposes of determining the # uniqueness of primary key values, NULL values are considered distinct # from all other values, including other NULLs.
R-06471-16287-47744-18667-43209-46484-64489-20920 tcl slt th3 src
If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows have identical primary key values, that is a constraint violation.
/* IMP: R-06471-16287 */
# EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts # to modify the table content so that two or more rows have identical # primary key values, that is a constraint violation.
R-40010-16873-27185-05661-21009-44505-33861-01436 tcl slt th3 src
Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column.
/* IMP: R-40010-16873 */
# EVIDENCE-OF: R-40010-16873 Unless the column is an INTEGER PRIMARY KEY # or the table is a WITHOUT ROWID table or a STRICT table or the column # is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY # column.
R-00227-21080-37302-15362-03285-23826-51380-28642 tcl slt th3 src
A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints.
/* IMP: R-00227-21080 */
# EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY # KEY constraint, except that a single table may have any number of # UNIQUE constraints.
R-30981-64168-44842-46448-61673-47635-40067-59553 tcl slt th3 src
For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint.
/* IMP: R-30981-64168 */
# EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table, # each row must contain a unique combination of values in the columns # identified by the UNIQUE constraint.
R-00404-17670-59562-50305-44521-33242-04500-48832 tcl slt th3 src
For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.
/* IMP: R-00404-17670 */
# EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints, # NULL values are considered distinct from all other values, including # other NULLs.
R-55072-00299-56529-46464-12562-60725-23596-59611 tcl slt th3 src
As with PRIMARY KEYs, a UNIQUE table-constraint clause must contain only column names — the use of expressions in an indexed-column of a UNIQUE table-constraint is not supported.
th3/cov1/index05.test:114
/* IMP: R-55072-00299 */
# EVIDENCE-OF: R-55072-00299 As with PRIMARY KEYs, a UNIQUE # table-constraint clause must contain only column names — the # use of expressions in an indexed-column of a UNIQUE table-constraint # is not supported.
R-55820-29984-60455-07962-21616-40941-09415-50372 tcl slt th3 src
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database.
/* IMP: R-55820-29984 */
# EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY # constraints are implemented by creating a unique index in the # database.
R-45493-35653-32389-26460-18465-38474-56599-09288 tcl slt th3 src
A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference.
/* IMP: R-45493-35653 */
# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference.
R-55435-14303-03333-49029-22084-10936-61067-18432 tcl slt th3 src
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.
/* IMP: R-55435-14303 */
# EVIDENCE-OF: R-55435-14303 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.
R-34109-39108-27898-16254-24563-58797-32171-17937 tcl slt th3 src
If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation.
/* IMP: R-34109-39108 */
# EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL, # or any other non-zero value, it is not a constraint violation.
R-44163-39688-38219-15448-17659-56666-21013-19183 tcl slt th3 src
The expression of a CHECK constraint may not contain a subquery.
th3/cov1/resolve06.test:68
/* IMP: R-44163-39688 */
# EVIDENCE-OF: R-44163-39688 The expression of a CHECK constraint may # not contain a subquery.
R-02060-64547-35959-63655-11509-53404-59087-08290 tcl slt th3 src
A NOT NULL constraint may only be attached to a column definition, not specified as a table constraint.
/* IMP: R-02060-64547 */
# EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached # to a column definition, not specified as a table constraint.
R-31795-57643-34892-44323-47602-12322-63634-37934 tcl slt th3 src
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.
/* IMP: R-31795-57643 */
# EVIDENCE-OF: R-31795-57643 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.
R-34093-09213-03056-31219-28057-06277-39986-53813 tcl slt th3 src
PRIMARY KEY, UNIQUE and NOT NULL constraints may be explicitly assigned another default conflict resolution algorithm by including a conflict-clause in their definitions.
/* IMP: R-34093-09213 */
# EVIDENCE-OF: R-34093-09213 PRIMARY KEY, UNIQUE and NOT NULL # constraints may be explicitly assigned another default conflict # resolution algorithm by including a conflict-clause in their # definitions.
R-17539-59899-08385-03908-29719-29448-39781-05413 tcl slt th3 src
Or, if a constraint definition does not include a conflict-clause, the default conflict resolution algorithm is ABORT.
/* IMP: R-17539-59899 */
# EVIDENCE-OF: R-17539-59899 Or, if a constraint definition does not # include a conflict-clause, the default conflict resolution algorithm # is ABORT.
R-29031-55583-59701-40159-49217-55206-58141-58993 tcl slt th3 src
For historical compatibility only, table CHECK constraints are allowed to have a conflict resolution clause, but that has no effect.
/* IMP: R-29031-55583 */
# EVIDENCE-OF: R-29031-55583 For historical compatibility only, table # CHECK constraints are allowed to have a conflict resolution clause, # but that has no effect.
R-19114-56113-41752-64271-30879-46460-35912-30160 tcl slt th3 src
Different constraints within the same table may have different default conflict resolution algorithms.
/* IMP: R-19114-56113 */
# EVIDENCE-OF: R-19114-56113 Different constraints within the same table # may have different default conflict resolution algorithms.
R-51414-32910-11625-05562-19787-23325-64160-35214 tcl slt th3 src
Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table.
/* IMP: R-51414-32910 */
# EVIDENCE-OF: R-51414-32910 Except for WITHOUT ROWID tables, all rows # within SQLite tables have a 64-bit signed integer key that uniquely # identifies the row within its table.
R-52584-04009-09183-39351-22068-55932-63814-54169 tcl slt th3 src
The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name.
/* IMP: R-52584-04009 */
# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one # of the special case-independent names "rowid", "oid", or "_rowid_" in # place of a column name.
R-26501-17306-17113-63025-42217-43181-46541-32131 tcl slt th3 src
If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.
/* IMP: R-26501-17306 */
# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column # named "rowid", "oid" or "_rowid_", then that name always refers the # explicitly declared column and cannot be used to retrieve the integer # rowid value.
R-47901-33947-04080-30794-42855-09326-15418-05201 tcl slt th3 src
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.
/* IMP: R-47901-33947 */
# EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid # table has a primary key that consists of a single column and the # declared type of that column is "INTEGER" in any mixture of upper and # lower case, then the column becomes an alias for the rowid.
R-41444-49665-13532-29136-56203-03908-49213-31629 tcl slt th3 src
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.
/* IMP: R-41444-49665 */
# EVIDENCE-OF: R-41444-49665 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.
R-45951-08347-03834-18503-21973-51666-50988-55652 tcl slt th3 src
if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key.
/* IMP: R-45951-08347 */
# EVIDENCE-OF: R-45951-08347 if the declaration of a column with # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does # not become an alias for the rowid and is not classified as an integer # primary key.
R-56094-57830-57463-55156-04028-13498-23873-32141 tcl slt th3 src
the following three table declarations all cause the column "x" to be an alias for the rowid (an integer primary key):
- CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
- CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
- CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
/* IMP: R-56094-57830 */
# EVIDENCE-OF: R-56094-57830 the following three table declarations all # cause the column "x" to be an alias for the rowid (an integer primary # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, # z, PRIMARY KEY(x DESC));
R-20149-25884-15163-16334-64710-02284-04877-02507 tcl slt th3 src
the following declaration does not result in "x" being an alias for the rowid:
- CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
/* IMP: R-20149-25884 */
# EVIDENCE-OF: R-20149-25884 the following declaration does not result # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY # KEY DESC, y, z);
R-03733-29734-34954-23072-53116-42399-24178-53918 tcl slt th3 src
Rowid values may be modified using an UPDATE statement in the same way as any other column value can, either using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by using an alias created by an integer primary key.
/* IMP: R-03733-29734 */
# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
# UPDATE statement in the same way as any other column value can, either
# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
# using an alias created by an integer primary key.
R-58706-14229-29588-03178-14400-15818-06936-07358 tcl slt th3 src
Similarly, an INSERT statement may provide a value to use as the rowid for each row inserted.
/* IMP: R-58706-14229 */
# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide # a value to use as the rowid for each row inserted.
R-32326-44592-25744-64223-64465-06212-39042-00138 tcl slt th3 src
Unlike normal SQLite columns, an integer primary key or rowid column must contain integer values. Integer primary key or rowid columns are not able to hold floating point values, strings, BLOBs, or NULLs.
/* IMP: R-32326-44592 */
# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer # primary key or rowid column must contain integer values. Integer # primary key or rowid columns are not able to hold floating point # values, strings, BLOBs, or NULLs.
R-64224-62578-03972-01663-15299-36598-00230-00133 tcl slt th3 src
If an UPDATE statement attempts to set an integer primary key or rowid column to a NULL or blob value, or to a string or real value that cannot be losslessly converted to an integer, a "datatype mismatch" error occurs and the statement is aborted.
/* IMP: R-64224-62578 */
# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an # integer primary key or rowid column to a NULL or blob value, or to a # string or real value that cannot be losslessly converted to an # integer, a "datatype mismatch" error occurs and the statement is # aborted.
R-05734-13629-21302-48631-57775-63447-36504-30462 tcl slt th3 src
If an INSERT statement attempts to insert a blob value, or a string or real value that cannot be losslessly converted to an integer into an integer primary key or rowid column, a "datatype mismatch" error occurs and the statement is aborted.
/* IMP: R-05734-13629 */
# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a # blob value, or a string or real value that cannot be losslessly # converted to an integer into an integer primary key or rowid column, a # "datatype mismatch" error occurs and the statement is aborted.
R-07986-46024-43778-44003-14770-46888-01136-33565 tcl slt th3 src
If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically.
/* IMP: R-07986-46024 */
# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a # NULL value into a rowid or integer primary key column, the system # chooses an integer value to use as the rowid automatically.
R-60116-07512-58915-34357-51591-00163-63646-47672 tcl slt th3 src
The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.
th3/req1/foreignkeys03.test:14
/* IMP: R-60116-07512 */
# EVIDENCE-OF: R-60116-07512 The parent key of a foreign key constraint # is not allowed to use the rowid. The parent key must used named # columns only.