Choose any three.
SQLite Requirement Matrix Details
withoutrowid.html
Index Summary Markup Original
R-36924-43758-45428-63164-00988-45045-48041-44762 tcl slt th3 src
By default, every row in SQLite has a special column, usually called the "rowid", that uniquely identifies that row within the table.
tcl/rowid.test:15 tcl/without_rowid5.test:19 th3/cov1/withoutrowid01.test:10
/* IMP: R-36924-43758 */
# EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a # special column, usually called the "rowid", that uniquely identifies # that row within the table.
R-32341-39358-46818-14772-56147-53577-21121-58224 tcl slt th3 src
However if the phrase "WITHOUT ROWID" is added to the end of a CREATE TABLE statement, then the special "rowid" column is omitted.
tcl/without_rowid5.test:23 th3/cov1/withoutrowid01.test:33
/* IMP: R-32341-39358 */
# EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is # added to the end of a CREATE TABLE statement, then the special "rowid" # column is omitted.
R-00217-01605-34207-18386-37017-37707-31164-60184 tcl slt th3 src
To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement. For example:
CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID;
tcl/without_rowid5.test:44 th3/cov1/withoutrowid01.test:57
/* IMP: R-00217-01605 */
# EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add # the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement. # For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY # KEY, cnt INTEGER ) WITHOUT ROWID;
R-24770-17719-20723-28193-15704-54316-24358-11027 tcl slt th3 src
As with all SQL syntax, the case of the keywords does not matter. One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and it will mean the same thing.
tcl/without_rowid5.test:60 th3/cov1/withoutrowid01.test:72
/* IMP: R-24770-17719 */
# EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the # keywords does not matter. One can write "WITHOUT rowid" or "without # rowid" or "WiThOuT rOwId" and it will mean the same thing.
R-58033-17334-23176-58899-39205-34616-00819-09634 tcl slt th3 src
An error is raised if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
tcl/without_rowid5.test:111 th3/cov1/withoutrowid01.test:95
/* IMP: R-58033-17334 */
# EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE # statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
R-01418-51310-13172-45959-27772-51956-15619-12685 tcl slt th3 src
However, only "rowid" works as the keyword in the CREATE TABLE statement.
tcl/without_rowid5.test:95 th3/cov1/withoutrowid01.test:108
/* IMP: R-01418-51310 */
# EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword # in the CREATE TABLE statement.
R-63443-09418-01239-16017-27553-21198-10588-27536 tcl slt th3 src
Every WITHOUT ROWID table must have a PRIMARY KEY.
tcl/without_rowid5.test:114 th3/cov1/withoutrowid01.test:98
/* IMP: R-63443-09418 */
# EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a # PRIMARY KEY.
R-27966-31616-60182-10304-08590-16090-08574-57683 tcl slt th3 src
An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results in an error.
tcl/without_rowid5.test:117 th3/cov1/withoutrowid01.test:101
/* IMP: R-27966-31616 */
# EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table # without a PRIMARY KEY results in an error.
R-48230-36247-21230-47058-09870-51505-35285-11048 tcl slt th3 src
The special behaviors associated "INTEGER PRIMARY KEY" do not apply on WITHOUT ROWID tables.
tcl/without_rowid5.test:127 th3/cov1/withoutrowid01.test:130
/* IMP: R-48230-36247 */
# EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER # PRIMARY KEY" do not apply on WITHOUT ROWID tables.
R-33142-02092-43419-10432-03484-54260-43121-37781 tcl slt th3 src
AUTOINCREMENT does not work on WITHOUT ROWID tables.
tcl/without_rowid5.test:143 th3/cov1/withoutrowid01.test:149
/* IMP: R-33142-02092 */
# EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT # ROWID tables.
R-53084-07740-56303-11732-40527-47126-44561-51023 tcl slt th3 src
An error is raised if the "AUTOINCREMENT" keyword is used in the CREATE TABLE statement for a WITHOUT ROWID table.
tcl/without_rowid5.test:146 th3/cov1/withoutrowid01.test:152
/* IMP: R-53084-07740 */
# EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT" # keyword is used in the CREATE TABLE statement for a WITHOUT ROWID # table.
R-27831-00579-12114-42310-13784-00983-37340-32180 tcl slt th3 src
NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID table.
tcl/without_rowid5.test:154 th3/cov1/withoutrowid01.test:160
/* IMP: R-27831-00579 */
# EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the # PRIMARY KEY in a WITHOUT ROWID table.
R-29781-51289-55954-01701-34718-07402-02199-48708 tcl slt th3 src
So, ordinary rowid tables in SQLite violate the SQL standard and allow NULL values in PRIMARY KEY fields.
tcl/without_rowid5.test:157 th3/cov1/withoutrowid01.test:163
/* IMP: R-29781-51289 */
# EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate # the SQL standard and allow NULL values in PRIMARY KEY fields.
R-27472-62612-19629-13155-08759-40565-10817-64469 tcl slt th3 src
But WITHOUT ROWID tables do follow the standard and will throw an error on any attempt to insert a NULL into a PRIMARY KEY column.
tcl/without_rowid5.test:160 th3/cov1/withoutrowid01.test:166
/* IMP: R-27472-62612 */
# EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the # standard and will throw an error on any attempt to insert a NULL into # a PRIMARY KEY column.
R-47220-63683-02784-26430-33183-60835-28174-15820 tcl slt th3 src
The sqlite3_last_insert_rowid() function does not work for WITHOUT ROWID tables.
tcl/lastinsert.test:39 th3/cov1/withoutrowid01.test:193
/* IMP: R-47220-63683 */
# EVIDENCE-OF: R-47220-63683 The sqlite3_last_insert_rowid() function # does not work for WITHOUT ROWID tables.
R-12643-30541-59599-14693-16305-56231-57085-12905 tcl slt th3 src
The incremental blob I/O mechanism does not work for WITHOUT ROWID tables.
tcl/without_rowid5.test:189 th3/cov1/vdbeblob01.test:326
/* IMP: R-12643-30541 */
# EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not # work for WITHOUT ROWID tables.
R-61808-14344-47257-03994-26724-50964-10589-13190 tcl slt th3 src
The sqlite3_update_hook() interface does not fire callbacks for changes to a WITHOUT ROWID table.
tcl/hook.test:194 th3/cov1/main15.test:181
/* IMP: R-61808-14344 */
# EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does # not fire callbacks for changes to a WITHOUT ROWID table.