The INTEGER PRIMARY KEY (with or without AUTOINCREMENT) is the rowid for the row in the table. You are just giving it an explicit column name by declaring it as a column. This allows you to, for example, refer to the rowid in referential contraints. Even if you do not explicity declare an INTEGER PRIMARY KEY, each table always has one. If you declare a PRIMARY KEY that is not an INTEGER PRIMARY KEY, that primary key is really just a UNIQUE constraint. The "true" PRIMARY KEY is the implicit rowid. This is because a table is stored in a btree structure and the rowid is the unique key into the btree which identifies a row.
Leaving a column/value unspecified in an insert operation is the same as explicitly specifying the column with a NULL value. That is, if a table has two columns, col1 and col2, the following inserts are identical: insert into table (col2) values (4); and insert into table (col1, col2) values (NULL, 4); and, they are processed identically and the materialized result will be identical. Obviously every row must have a unique rowid (because it is the primary key for the underlying btree storage of the row), and the AUTOINCREMENT just modifies how the new explicitly named rowid is generated, if it is NULL on an insert. The default, used when you do not specify an explicitly named rowid column, is equivalent to rowid INTEGER PRIMARY KEY which means that a new rowid will be max(rowid)+1 from the table at the time the row is materialized. So if you insert a row, then delete that row, then insert another row, the rowid's will be the same for the two different rows if at the time the insert was materialized max(rowid)+1 evaluated to the same result for both insert operations. Obviously, there will not be duplicate rowid's, but there will not be a missing rowid corresponding to the deleted row. If the rowid is defined as rowid INTEGER PRIMARY KEY AUTOINCREMENT, the insert/delete/insert will result in the the two inserted records having different increasing rowid's -- the row that was deleted will not have its unique rowid reused, and that rowid will no longer exist in the table. The key concept is that AUTOINCREMENT changes the automatic rowid selection algorithm from using max(rowid) to using a hi-water mark as the basis for selection of the new rowid. Whether or not you need to use autoincrement is dictated by the application/business logic. If you are, for example, using the rowid externally (for example, syncronizing data with external datastores) then you may not want the rowid of deleted rows being implicitly reused (any rowid can always be explicitly used). In other cases, it will simply not matter that different row instances might reuse rowid's. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Brandon Pimenta > Sent: Saturday, 04 August, 2012 08:28 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] AUTO_INCREMENT error > > Thank you for this advice! > > On Fri, Aug 3, 2012 at 9:24 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > > > That is not quite correct Simon. INTEGER PRIMARY KEY is always "auto > > incrementing" in that a new key is always one larger than the current max() > > key in the table. However, INTEGER PRIMARY KEY AUTOINCREMENT means that > > the key is always one larger than *any key that has ever existed* in the > > table. > > > > In other words, INTEGER PRIMARY KEY AUTOINCREMENT keys are uniquely > > ascending and will never be re-used. Without AUTOINCREMENT the key may be > > re-used. On a "different" record. > > > > > > --- > > () ascii ribbon campaign against html e-mail > > /\ www.asciiribbon.org > > > > > -----Original Message----- > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > > boun...@sqlite.org] On Behalf Of Simon Slavin > > > Sent: Friday, 03 August, 2012 10:00 > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] AUTO_INCREMENT error > > > > > > > > > On 3 Aug 2012, at 4:53pm, Brandon Pimenta <brandonskypime...@gmail.com> > > > wrote: > > > > > > > When using the SQL query > > > > > > > > CREATE TABLE test ( > > > > test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT > > > > ); > > > > > > > > or > > > > > > > > CREATE TABLE test ( > > > > test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT > > > > ); > > > > > > > > I will get the same error. > > > > > > All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT. You > > should > > > not specify it yourself. > > > > > > Also, SQLite will never itself assign a NULL to any of the values. > > Though I > > > can see that you might want NOT NULL in there to stop it being done by a > > > program which assigns its own value to test_1. > > > > > > Simon. > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
