sqlite3/www/autoinc.tcl

   1 #
   2 # Run this Tcl script to generate the autoinc.html file.
   3 #
   4 set rcsid {$Id: }
   5 source common.tcl
   6
   7 if {[llength $argv]>0} {
   8   set outputdir [lindex $argv 0]
   9 } else {
  10   set outputdir ""
  11 }
  12
  13 header {SQLite Autoincrement}
  14 puts {
  15 <h1>SQLite Autoincrement</h1>
  16
  17 <p>
  18 In SQLite, every row of every table has an integer ROWID.
  19 The ROWID for each row is unique among all rows in the same table.
  20 In SQLite version 2.8 the ROWID is a 32-bit signed integer.
  21 Version 3.0 of SQLite expanded the ROWID to be a 64-bit signed integer.
  22 </p>
  23
  24 <p>
  25 You can access the ROWID of an SQLite table using one the special column
  26 names ROWID, _ROWID_, or OID.
  27 Except if you declare an ordinary table column to use one of those special
  28 names, then the use of that name will refer to the declared column not
  29 to the internal ROWID.
  30 </p>
  31
  32 <p>
  33 If a table contains a column of type INTEGER PRIMARY KEY, then that
  34 column becomes an alias for the ROWID.  You can then access the ROWID
  35 using any of four different names, the original three names described above
  36 or the name given to the INTEGER PRIMARY KEY column.  All these names are
  37 aliases for one another and work equally well in any context.
  38 </p>
  39
  40 <p>
  41 When a new row is inserted into an SQLite table, the ROWID can either
  42 be specified as part of the INSERT statement or it can be assigned
  43 automatically by the database engine.  To specify a ROWID manually,
  44 just include it in the list of values to be inserted.  For example:
  45 </p>
  46
  47 <blockquote><pre>
  48 CREATE TABLE test1(a INT, b TEXT);
  49 INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
  50 </pre></blockquote>
  51
  52 <p>
  53 If no ROWID is specified on the insert, an appropriate ROWID is created
  54 automatically.  The usual algorithm is to give the newly created row
  55 a ROWID that is one larger than the largest ROWID in the table prior
  56 to the insert.  If the table is initially empty, then a ROWID of 1 is
  57 used.  If the largest ROWID is equal to the largest possible integer
  58 (9223372036854775807 in SQLite version 3.0 and later) then the database
  59 engine starts picking candidate ROWIDs at random until it finds one
  60 that is not previously used.
  61 </p>
  62
  63 <p>
  64 The normal ROWID selection algorithm described above
  65 will generate monotonically increasing
  66 unique ROWIDs as long as you never use the maximum ROWID value and you never
  67 delete the entry in the table with the largest ROWID.
  68 If you ever delete rows or if you ever create a row with the maximum possible
  69 ROWID, then ROWIDs from previously deleted rows might be reused when creating
  70 new rows and newly created ROWIDs might not be in strictly accending order.
  71 </p>
  72
  73
  74 <h2>The AUTOINCREMENT Keyword</h2>
  75
  76 <p>
  77 If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
  78 different ROWID selection algorithm is used.
  79 The ROWID chosen for the new row is one larger than the largest ROWID
  80 that has ever before existed in that same table.  If the table has never
  81 before contained any data, then a ROWID of 1 is used.  If the table
  82 has previously held a row with the largest possible ROWID, then new INSERTs
  83 are not allowed and any attempt to insert a new row will fail with an
  84 SQLITE_FULL error.
  85 </p>
  86
  87 <p>
  88 SQLite keeps track of the largest ROWID that a table has ever held using
  89 the special SQLITE_SEQUENCE table.  The SQLITE_SEQUENCE table is created
  90 and initialized automatically whenever a normal table that contains an
  91 AUTOINCREMENT column is created.  The content of the SQLITE_SEQUENCE table
  92 can be modified using ordinary UPDATE, INSERT, and DELETE statements.
  93 But making modifications to this table will likely perturb the AUTOINCREMENT
  94 key generation algorithm.  Make sure you know what you are doing before
  95 you undertake such changes.
  96 </p>
  97
  98 <p>
  99 The behavior implemented by the AUTOINCREMENT keyword is subtly different
 100 from the default behavior.  With AUTOINCREMENT, rows with automatically
 101 selected ROWIDs are guaranteed to have ROWIDs that have never been used
 102 before by the same table in the same database.  And the automatically generated
 103 ROWIDs are guaranteed to be monotonically increasing.  These are important
 104 properties in certain applications.  But if your application does not
 105 need these properties, you should probably stay with the default behavior
 106 since the use of AUTOINCREMENT requires additional work to be done
 107 as each row is inserted and thus causes INSERTs to run a little slower.
 108 }
 109 footer $rcsid