On Fri, 28 Mar 2014 12:56:59 +0100 "Kleiner Werner" <sqliteh...@web.de> wrote:
> > > Hello > I saw some discussions here about using the word "AUTOINCREMENT" for > a primary key or not. > Sometimes the meaning is "INTEGER PRIMARY KEY" is enough, because > this will also make an auto increment id. > Can someone give me a clarification if "AUTOINCREMENT" is needed or > not? In the FAQ I can read this; > " The new key will be unique over all keys currently in the table, > but it might overlap with keys that have been previously deleted from > the table." Does this mean, if there are 10 rows with id 1 -10 and I > delete row with id 5, then the next insert will be ID = 5? Or is next > ID = 11? > I understand that if I need absolutely unique IDs over the lifetime I > need to use AUTOINCREMENT. That's correct? And with AUTOINCREMENT the > last IDs will be stored in a table "sqlite_sequence"? > [https://3c.web.de/mail/client/dereferrer?redirectUrl=http%3A%2F%2Fsqlite.org%2Ffileformat2.html%23seqtab] > Are there sow disatvantages of using AUTOINCREMENT , like SQLite is > slower or something else? best regard As I'm who said "AUTOINCREMENT" is not needed, I'll answer too. In your example, if it's defined as INTEGER PRIMARY KEY, next one may be 5, but not always. If you add "AUTOINCREMENT" it must be 11. You decide if you need it or not based on your use case. Other people add "AUTOINCREMENT" by default in all cases. For example, if you store mails in a table, you want their id with "AUTOINCREMENT" because this manner they are always sorted in recepction order. If a mail is deleted, a new one will be the max(id)+1, and not between mails 3 months ago. But if you store other data and from time to time you truncate data tables and rebuild the database, you may want new data begin with 0 instead. Or if you need to have a top/last id and all others must be smaller. Sqlite isn't slower if use "AUTOINCREMENT", if use it, it must query sqlite_secuence table, if not, it must search an empty id and both data are on memory often. Summary, it's not safer nor unsafer, faster or slower, it depends on your use case. HTH --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
