SQLite Requirement Matrix Details
autoinc.html

Index Summary Markup Original

R-28870-48866-03452-03570-43153-10694-10376-44715 tcl slt th3 src

In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table.

th3/req1/autoinc01.test:10

/* IMP: R-28870-48866 */

# EVIDENCE-OF: R-28870-48866 In SQLite, table rows normally have a
# 64-bit signed integer ROWID which is unique among all rows in the same
# table.

R-23612-42210-08607-14273-01110-55544-62379-11788 tcl slt th3 src

You can access the ROWID of an SQLite table using one of the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.

th3/req1/autoinc01.test:36

/* IMP: R-23612-42210 */

# EVIDENCE-OF: R-23612-42210 You can access the ROWID of an SQLite table
# using one of the special column names ROWID, _ROWID_, or OID. Except
# if you declare an ordinary table column to use one of those special
# names, then the use of that name will refer to the declared column not
# to the internal ROWID.

R-38485-20010-44949-34877-39366-11397-10597-42284 tcl slt th3 src

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID.

th3/req1/autoinc01.test:67

/* IMP: R-38485-20010 */

# EVIDENCE-OF: R-38485-20010 If a table contains a column of type
# INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID.

R-05038-25064-50136-23943-62126-35147-57737-05371 tcl slt th3 src

You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column.

th3/req1/autoinc01.test:76

/* IMP: R-05038-25064 */

# EVIDENCE-OF: R-05038-25064 You can then access the ROWID using any of
# four different names, the original three names described above or the
# name given to the INTEGER PRIMARY KEY column.

R-37283-61388-33413-37250-53520-10226-00265-15596 tcl slt th3 src

All these names are aliases for one another and work equally well in any context.

th3/req1/autoinc01.test:87

/* IMP: R-37283-61388 */

# EVIDENCE-OF: R-37283-61388 All these names are aliases for one another
# and work equally well in any context.

R-54260-17937-27956-02792-29631-43521-06975-25593 tcl slt th3 src

When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine.

th3/req1/autoinc01.test:103

/* IMP: R-54260-17937 */

# EVIDENCE-OF: R-54260-17937 When a new row is inserted into an SQLite
# table, the ROWID can either be specified as part of the INSERT
# statement or it can be assigned automatically by the database engine.

R-51090-01319-14064-12731-11996-25271-12036-18507 tcl slt th3 src

To specify a ROWID manually, just include it in the list of values to be inserted. For example:

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

th3/req1/autoinc01.test:120

/* IMP: R-51090-01319 */

# EVIDENCE-OF: R-51090-01319 To specify a ROWID manually, just include
# it in the list of values to be inserted. For example: CREATE TABLE
# test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5,
# 'hello');

R-12104-35971-65257-58163-03125-32650-03522-02366 tcl slt th3 src

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically.

th3/req1/autoinc01.test:133

/* IMP: R-12104-35971 */

# EVIDENCE-OF: R-12104-35971 If no ROWID is specified on the insert, or
# if the specified ROWID has a value of NULL, then an appropriate ROWID
# is created automatically.

R-29538-34987-07238-64188-64158-06889-50248-62239 tcl slt th3 src

The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert.

src/vdbe.c:4243   th3/req1/autoinc01.test:137

/* IMP: R-29538-34987 */

# EVIDENCE-OF: R-29538-34987 The usual algorithm is to give the newly
# created row a ROWID that is one larger than the largest ROWID in the
# table prior to the insert.

R-61914-48074-43351-14056-51344-34537-46858-37537 tcl slt th3 src

If the table is initially empty, then a ROWID of 1 is used.

src/vdbe.c:4236   th3/req1/autoinc01.test:157

/* IMP: R-61914-48074 */

# EVIDENCE-OF: R-61914-48074 If the table is initially empty, then a
# ROWID of 1 is used.

R-07677-41881-40135-26272-16926-18163-28954-03069 tcl slt th3 src

If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used.

src/vdbe.c:4279   th3/cov1/vdbe23.test:35   th3/req1/autoinc01.test:166

/* IMP: R-07677-41881 */

# EVIDENCE-OF: R-07677-41881 If the largest ROWID is equal to the
# largest possible integer (9223372036854775807) then the database
# engine starts picking positive candidate ROWIDs at random until it
# finds one that is not previously used.

R-38219-53002-00313-45584-37253-57007-40981-19888 tcl slt th3 src

If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error.

src/vdbe.c:4295   th3/cov1/vdbe23.test:40

/* IMP: R-38219-53002 */

# EVIDENCE-OF: R-38219-53002 If no unused ROWID can be found after a
# reasonable number of attempts, the insert operation fails with an
# SQLITE_FULL error.

R-40812-03570-58737-26450-04451-50021-12578-43661 tcl slt th3 src

If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.

src/vdbe.c:4298   th3/cov1/vdbe23.test:56

/* IMP: R-40812-03570 */

# EVIDENCE-OF: R-40812-03570 If no negative ROWID values are inserted
# explicitly, then automatically generated ROWID values will always be
# greater than zero.

R-60470-29837-23817-60993-08685-62249-11012-03852 tcl slt th3 src

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID.

th3/req1/autoinc01.test:141

/* IMP: R-60470-29837 */

# EVIDENCE-OF: R-60470-29837 The normal ROWID selection algorithm
# described above will generate monotonically increasing unique ROWIDs
# as long as you never use the maximum ROWID value and you never delete
# the entry in the table with the largest ROWID.

R-03793-19254-25835-35208-42396-01402-31770-31653 tcl slt th3 src

If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

th3/req1/autoinc01.test:178

/* IMP: R-03793-19254 */

# EVIDENCE-OF: R-03793-19254 If you ever delete rows or if you ever
# create a row with the maximum possible ROWID, then ROWIDs from
# previously deleted rows might be reused when creating new rows and
# newly created ROWIDs might not be in strictly ascending order.

R-43328-56724-38269-63059-47788-20047-48832-13506 tcl slt th3 src

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used.

th3/req1/autoinc01.test:196

/* IMP: R-43328-56724 */

# EVIDENCE-OF: R-43328-56724 If a column has the type INTEGER PRIMARY
# KEY AUTOINCREMENT then a slightly different ROWID selection algorithm
# is used.

R-27225-50545-05403-13853-61405-64254-57109-20457 tcl slt th3 src

The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before been inserted in that same table.

th3/req1/autoinc01.test:200

/* IMP: R-27225-50545 */

# EVIDENCE-OF: R-27225-50545 The ROWID chosen for the new row is at
# least one larger than the largest ROWID that has ever before been
# inserted in that same table.

R-15283-49521-12983-52199-46141-26319-45151-22196 tcl slt th3 src

If the table has never before contained any data, then a ROWID of 1 is used.

th3/req1/autoinc01.test:204

/* IMP: R-15283-49521 */

# EVIDENCE-OF: R-15283-49521 If the table has never before contained any
# data, then a ROWID of 1 is used.

R-17817-00630-38271-37218-33911-49615-48775-23865 tcl slt th3 src

If the largest possible ROWID has previously been inserted, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error.

th3/req1/autoinc01.test:321

/* IMP: R-17817-00630 */

# EVIDENCE-OF: R-17817-00630 If the largest possible ROWID has
# previously been inserted, then new INSERTs are not allowed and any
# attempt to insert a new row will fail with an SQLITE_FULL error.

R-17684-03275-18018-08876-61159-17187-00685-37913 tcl slt th3 src

Only ROWID values from previous transactions that were committed are considered. ROWID values that were rolled back are ignored and can be reused.

th3/req1/autoinc01.test:231

/* IMP: R-17684-03275 */

# EVIDENCE-OF: R-17684-03275 Only ROWID values from previous
# transactions that were committed are considered. ROWID values that
# were rolled back are ignored and can be reused.

R-09328-38790-35467-08457-46231-05327-56817-51584 tcl slt th3 src

SQLite keeps track of the largest ROWID using an internal table named "sqlite_sequence".

th3/req1/autoinc01.test:245

/* IMP: R-09328-38790 */

# EVIDENCE-OF: R-09328-38790 SQLite keeps track of the largest ROWID
# using an internal table named "sqlite_sequence".

R-43700-31930-54080-27091-31266-57199-30903-39458 tcl slt th3 src

The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created.

th3/req1/autoinc01.test:262

/* IMP: R-43700-31930 */

# EVIDENCE-OF: R-43700-31930 The sqlite_sequence table is created and
# initialized automatically whenever a normal table that contains an
# AUTOINCREMENT column is created.

R-20486-33667-53440-39666-65031-09323-37959-22013 tcl slt th3 src

The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements.

th3/req1/autoinc01.test:290

/* IMP: R-20486-33667 */

# EVIDENCE-OF: R-20486-33667 The content of the sqlite_sequence table
# can be modified using ordinary UPDATE, INSERT, and DELETE statements.

R-26490-64257-21643-35537-33187-49961-28588-55610 tcl slt th3 src

But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm.

th3/req1/autoinc01.test:293

/* IMP: R-26490-64257 */

# EVIDENCE-OF: R-26490-64257 But making modifications to this table will
# likely perturb the AUTOINCREMENT key generation algorithm.

R-57773-31134-13859-63861-60262-03843-19674-02031 tcl slt th3 src

With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database.

th3/req1/autoinc01.test:343

/* IMP: R-57773-31134 */

# EVIDENCE-OF: R-57773-31134 With AUTOINCREMENT, rows with automatically
# selected ROWIDs are guaranteed to have ROWIDs that have never been
# used before by the same table in the same database.

R-47208-15314-60187-26132-59357-10692-55881-38588 tcl slt th3 src

And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

th3/req1/autoinc01.test:347

/* IMP: R-47208-15314 */

# EVIDENCE-OF: R-47208-15314 And the automatically generated ROWIDs are
# guaranteed to be monotonically increasing.

R-56805-28429-09038-27116-20961-52646-32004-47216 tcl slt th3 src

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one.

th3/req1/autoinc01.test:355

/* IMP: R-56805-28429 */

# EVIDENCE-OF: R-56805-28429 Note that "monotonically increasing" does
# not imply that the ROWID always increases by exactly one.

R-50658-17415-26867-64238-49618-34933-22371-53108 tcl slt th3 src

One is the usual increment.

th3/req1/autoinc01.test:349

/* IMP: R-50658-17415 */

# EVIDENCE-OF: R-50658-17415 One is the usual increment.

R-15078-25200-47597-45134-52626-09709-11803-57181 tcl slt th3 src

However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence.

th3/req1/autoinc01.test:358

/* IMP: R-15078-25200 */

# EVIDENCE-OF: R-15078-25200 However, if an insert fails due to (for
# example) a uniqueness constraint, the ROWID of the failed insertion
# attempt might not be reused on subsequent inserts, resulting in gaps
# in the ROWID sequence.

R-24433-42760-21501-54800-20604-32904-26746-52498 tcl slt th3 src

AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

th3/req1/autoinc01.test:363

/* IMP: R-24433-42760 */

# EVIDENCE-OF: R-24433-42760 AUTOINCREMENT guarantees that automatically
# chosen ROWIDs will be increasing but not that they will be sequential.

R-46912-28798-53031-38709-36895-29366-07860-41608 tcl slt th3 src

Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

th3/req1/autoinc01.test:379

/* IMP: R-46912-28798 */

# EVIDENCE-OF: R-46912-28798 Any attempt to use AUTOINCREMENT on a
# WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY
# column results in an error.