Thanks, Meredydd! That’s actually more stable than I was expecting! Many databases will reuse row ids, after a delete, unless you take (database-specific) extra steps.
If I may summarize, I think the underlying lesson (for developers) here is simple. A row id identifies one row in one table. Period. Its suitability for identifying anything else will depend on the size, reach, and lifetime of the application. We can define two categories:
-
My app has only one table, in one database, containing the sole copy of the definition of some external entity (e.g., a user, a product order). In this case, the row id can be a good proxy for the entity’s own id. At least, it will have the same stability and the same lifetime.
-
I need to identify such an entity across multiple contexts (databases, tables, or apps, or multiple versions of the above). In that case, to tie the contexts together, I have the choice of a cross-reference table, a cross-context id, or both.
Apps often live far longer, and reach into more contexts, than originally intended. So we should be aware, and watch for, those occasions when a Category 1 table slips into Category 2.
When this happens, a UUID can make a good cross-context id. It may be overkill in many cases, but at least it should work. There are a variety of UUID schemes, suitable for different occasions.