~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

<h1>SQLite Autoincrement</h1>

122

123

124

<p>

125

In SQLite, every row of every table has an 64-bit signed integer <a href="lang_createtable.html#rowid">ROWID</a>.

126

The ROWID for each row is unique among all rows in the same table.

127

</p>

128

129

<p>

130

You can access the ROWID of an SQLite table using one the special column

131

names ROWID, _ROWID_, or OID.

132

Except if you declare an ordinary table column to use one of those special

133

names, then the use of that name will refer to the declared column not

134

to the internal ROWID.

135

</p>

136

137

<p>

138

If a table contains a column of type <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, then that

139

column becomes an alias for the ROWID. You can then access the ROWID

140

using any of four different names, the original three names described above

141

or the name given to the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column. All these names are

142

aliases for one another and work equally well in any context.

143

</p>

144

145

<p>

146

When a new row is inserted into an SQLite table, the ROWID can either

147

be specified as part of the INSERT statement or it can be assigned

148

automatically by the database engine. To specify a ROWID manually,

149

just include it in the list of values to be inserted. For example:

150

</p>

151

152

153

CREATE TABLE test1(a INT, b TEXT);

154

INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

155

</pre></blockquote>

156

157

<p>

158

If no ROWID is specified on the insert, or if the specified ROWID has a value

159

of NULL, then an appropriate ROWID is created

160

automatically. The usual algorithm is to give the newly created row

161

a ROWID that is one larger than the largest ROWID in the table prior

162

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

163

used. If the largest ROWID is equal to the largest possible integer

164

(9223372036854775807) then the database

165

engine starts picking positive candidate ROWIDs at random until it finds one

166

that is not previously used.

167

If no unused ROWID can be found after a reasonable number of attempts,

168

the insert operation fails with an <a href="c3ref/c_abort.html">SQLITE_FULL</a> error.

169

If no negative ROWID values are inserted explicitly, then automatically

170

generated ROWID values will always be greater than zero.

171

</p>

172

173

<p>

174

The normal ROWID selection algorithm described above

175

will generate monotonically increasing

176

unique ROWIDs as long as you never use the maximum ROWID value and you never

177

delete the entry in the table with the largest ROWID.

178

If you ever delete rows or if you ever create a row with the maximum possible

179

ROWID, then ROWIDs from previously deleted rows might be reused when creating

180

new rows and newly created ROWIDs might not be in strictly ascending order.

181

</p>

182

183

184

<h2>The AUTOINCREMENT Keyword</h2>

185

186

<p>

187

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly

188

different ROWID selection algorithm is used.

189

The ROWID chosen for the new row is at least one larger than the largest ROWID

190

that has ever before existed in that same table. If the table has never

191

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

192

has previously held a row with the largest possible ROWID, then new INSERTs

193

are not allowed and any attempt to insert a new row will fail with an

194

SQLITE_FULL error.

195

</p>

196

197

<p>

198

SQLite keeps track of the largest ROWID that a table has ever held using

199

an <a href="fileformat2.html#intschema">internal table</a> named "<a href="fileformat2.html#seqtab">sqlite_sequence</a>".

200

The sqlite_sequence table is created

201

and initialized automatically whenever a normal table that contains an

202

AUTOINCREMENT column is created. The content of the sqlite_sequence table

203

can be modified using ordinary UPDATE, INSERT, and DELETE statements.

204

But making modifications to this table will likely perturb the AUTOINCREMENT

205

key generation algorithm. Make sure you know what you are doing before

206

you undertake such changes.

207

</p>

208

209

<p>

210

The behavior implemented by the AUTOINCREMENT keyword is subtly different

211

from the default behavior. With AUTOINCREMENT, rows with automatically

212

selected ROWIDs are guaranteed to have ROWIDs that have never been used

213

before by the same table in the same database. And the automatically generated

214

ROWIDs are guaranteed to be monotonically increasing. These are important

215

properties in certain applications. But if your application does not

216

need these properties, you should probably stay with the default behavior

217

since the use of AUTOINCREMENT requires additional work to be done

218

as each row is inserted and thus causes INSERTs to run a little slower.

219

</p>

220

221

<p>Note that "monotonically increasing" does not imply that the ROWID always

222

increases by exactly one. One is the usual increment. However, if an

223

insert fails due to (for example) a uniqueness constraint, the ROWID of

224

the failed insertion attempt might not be reused on subsequent inserts,

225

resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that

226

automatically chosen ROWIDs will be increasing but not that they will be

227

sequential.</p>

228