On Thu, Jan 07, 2010 at 03:39:42PM +0000, Simon Slavin scratched on the wall: > > On 7 Jan 2010, at 1:15pm, Jay A. Kreibich wrote: > > > There isn't a correct way of doing this. You need to manually loop > > over the SELECT, do the INSERT, and call sqlite3_last_insert_rowid() > > (or the SQL function last_insert_rowid()) after each INSERT to build > > up a collection of ROWIDs. > > One other aspect is this: you are taking what should be an internal > function of SQLite and using it for your own purposes.
Both the C and SQL functions are there to be used. Proper foreign key support is nearly impossible without them. I'd guess that's why the SQL version of the function exists. > This is sometimes a bad idea. Well, yes, just about anything can be abused. The sqlite3_sequence table, for example. > If you want to have a column with particular > values in which you use for your own purposes, make your own column > and put whatever values into it you want. Yes and no. Using the raw ROWID column for application purposes-- especially foreign keys-- is not a good idea. One .dump or VACUUM and you're screwed. That said, within the SQLite environment the proper and recognized way of doing unique, auto-generated ID values is using an INTEGER PRIMARY KEY, and it is well understood that this is a ROWID alias. If you want to setup foreign key references (the most common reason to need the generated ID value of a row that was just INSERTed, and what I assume the OP is trying to do), you need a function like this or some other way to get the new ROWID-- that is, the new application defined ID-- for this table. So proper use does make some assumptions. For starters, the rows should be INSERTed into a table that has an INTEGER PRIMARY KEY. In other words, the function should be thought of as "sqlite3_last_insert_integer_primary_key()" (but I'm really glad it isn't named that). Second, if you INSERT a record, grab the ROWID/PRIMARY KEY via sqlite3_last_insert_rowid(), and then INSERT one or more rows into a different table that contains a foreign key reference back to the original table, you should do that whole sequence within a transaction to make sure you get the proper "last" ROWID. Of course, you should likely be doing that all within a transaction anyways. At the very least, do the initial INSERT and get-last in a single transaction. Also be careful with triggers. Triggers can muck everything up by changing the "last." -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
