On Mon, Apr 16, 2012 at 12:58 PM, Puneet Kishor <[email protected]> wrote: > I am experimenting with a home-grown versioning system where every > "significant" modification to row would be performed on a copy of the row, > the original being preserved. So, if I have
There are several ways to handle this. You could denormalize the ID into a separate table that holds... just the ID, so that way you get your autoincrement. Or you could use a trigger to set the ID column (which means you must allow it to be NULL) to the max() + 1 of the IDs. and you'll need the ID to be first in some index (or the composite key) so that you can make that max() run efficiently. The denormalization-of-the-ID approach also lets you create other sorts of stable identifiers besides integers, such as UUIDs, say. You'll need VIEWs to filter out all but current data. Simon suggests moving the historical data into separate tables, which is a good idea, except that if you want to have future changes pre-created and take effect as time passes then the separate tables schema doesn't work very well. Nico -- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
