Originally posted by Mark Smit
View Post
In some database engines RowID will change on you if you compact the database or if the highest rowID is deleted, it can be reused (See SQLite note below).
In some dbs RowID is read only. There is no way to set to a specific value. If you were to export / import or merge two databases you would not be able to use the same RowIDs. Of course, you could export the data and related table data in order without ids, but how would you load the data making sure the new RowID assigned is the assigned Foreign key in other tables.
If you have your own column you can at least set the CustomID to an offset during import/merge and include a unique ID in the exported data.
Not all databases have a RowID.
A custom column used as a primary key, doesn't have to be numeric.
From the SQLite Docs
The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
In other words, if you have a database with RowIds 1-10,000 and you delete 5000-10,000. The next record inserted will have a RowID of 5,000.
Leave a comment: