>> [...] does >> >> db last_insert_rowid >> >> reliably return the_key of the most recently inserted database row, >> so that the returned value may safely (across vacuums etc) be used >> as a foreign reference to t's the_key column? > > Yes.
Actually there *is* a caveat, which is that if the insert does not actually complete -- for example, you have a trigger on insert that could raise an 'ignore' -- then the last_insert_rowid will still return the rowid of the last row that was actually inserted (as you would expect), but then if you blindly use this in a foreign key constraint without checking whether the row you wanted *was* inserted, you may find you reference the wrong row... SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table tab(i); sqlite> create trigger trig before insert on tab when new.i=5 begin ...> select raise(ignore); ...> end; sqlite> insert into tab values(10); sqlite> select last_insert_rowid(); 1 sqlite> insert into tab values(20); sqlite> select last_insert_rowid(); 2 sqlite> insert into tab values(5); sqlite> select last_insert_rowid(); 2 sqlite> Just a word of caution, although it may not apply to your situation... ;o) However, there are work-arounds to the above 'caveat'. Rgds Andy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
