12/22/2023 0 Comments Sqlite autoincrement start value![]() If the table has never before contained any data, then a ROWID of 1 is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. 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. 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. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. 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. If the table is initially empty, then a ROWID of 1 is used. 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. 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. INSERT INTO test1(rowid, a, b) VALUES( 123, 5, 'hello') For example: CREATE TABLE test1(a INT, b TEXT) ![]() To specify a ROWID manually, just include it in the list of values to be inserted. 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. All these names are aliases for one another and work equally well in any context. 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. If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. 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. You can access the ROWID of an SQLite table using one of the special column names ROWID, _ROWID_, or OID. ![]() ( WITHOUT ROWID tables are the exception.) ![]() In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. This is true regardless of whether or not the AUTOINCREMENT keyword is used. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. ![]() In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |