Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet > <[EMAIL PROTECTED]> wrote: > > If you designate an integer column as also being the primary key, > > then SQLite will auto assign its value incrementally each time > > you insert a new row, unless you assign a value explicitly. > > In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"? > Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this > column if it's NULL?
The AUTOINCREMENT keyword prevents an primary key from being reused even after it is deleted. Consider: /* 1 */ INSERT INTO table(pk, x) VALUES(NULL, 'row one'); /* 2 */ DELETE FROM table WHERE x='row one'; /* 3 */ INSERT INTO table(pk, x) VALUES(NULL, 'row two'); If pk is just a PRIMARY KEY (without AUTOINCREMENT) then the inserts at 1 and 3 will both create rows with the same primary key value. But if the AUTOINCREMENT keyword is used, then the insert at 3 will have a distinct primary key value from the insert at 1. -- D. Richard Hipp <[EMAIL PROTECTED]> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users