On Thu, Apr 28, 2011 at 5:20 PM, Dave Hayden <[email protected]> wrote: > On Apr 28, 2011, at 12:41 PM, Pavel Ivanov wrote: >>> After more poking, it appears that rowids might not be changed by a vacuum >>> if I have an index on the table. Is this true? If so, is it something I can >>> rely on going forward? >> >> No, it's not true. The only way to keep your rowids intact is to declare an >> INTEGER PRIMARY KEY alias for it. And you better never reference "rowid" >> name in your application or your database schema. > > Can you explain this in more detail? I've never seen any prohibition on using > "rowid" in the SQLite docs before. The page on autoincrement says > > "You can access the ROWID of an SQLite table using one the special column > names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column > to use one of those special names, then the use of that name will refer to > the declared column not to the internal ROWID." > > which suggests that referring to rowids is fine. If I add a "rowid integer > primary key" column on my tables, it seems like everything would work the way > I want it to with minimal code changes. Any reason that won't work?
As long as your 'rowid' column is declared as an INTEGER PRIMARY KEY, then yes, VACUUM will preserve rowids and you'll be able to use the rowid column name without other issues. Others have pointed out that using 'rowid' for any column other than an INTEGER PRIMARY KEY column would be confusing, but that's not what you're after. But even so, using 'rowid' is probably not in your best interest, not if you could use a more descriptive name, for example. Nico -- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
