Small. Fast. Reliable.
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:338

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