If you are using your primary key for anything other than a unique identifier for rows, you are almost certainly doing something wrong. If you need an index on creation time, or just creation order, have a creation time column and an index on that.
I like SQLite’s decision to make integer primary keys not marked as auto increment to simply be aliases for its rowid which is not guaranteed to be unique for the table’s lifetime.
i.e. inserts that create rows with id 1,2,3,4 a delete of row 3,4 followed by inserts will create new rows 3,4
> Mistaking and ID for something more is the best preparation for some poor soul's headache at some point in the (possibly distant) future.
Agreed, all IDs should be considered opaque. All user facing IDs should definitely be opaque and leak no information.
I’m not a fan of UUIDv7, and don’t buy any of the arguments in favor of it. v4 is the better ID. My general preference though is 128 random bits encoded as base58 for user display. v4 isn’t even 128 random bits!
Need creation time? Add a column. Need sequential ordering based on when rows were inserted? Add an auto increment column.
“But my indexes” So what? Prove that it realistically impacts performance (spoiler alert, the rest of your app is likely far slower than the user facing ID lookups!)
“But my foreign keys” Add a column. If you’re only in a single database server this can be the real primary key, likely an u32 or u64 if you truly need more than 4 billion rows.
“But my hyper scale!” Then eat the cost of more servers for your distributed system. You can’t have your cake and eat it too.
That to me sounds like a good way to amplify a bug whereby a foreign key has not been defined¹ where it should be. I hope there are copious warnings about it in the docs and when creating a table that way.
--------
[1] Or can't be as the referrer is in an external system, though in that case you probably want a separate external ID, possibly a UUID of some sort.
Good, as it should be.
> I hope there are copious warnings about it in the docs and when creating a table that way.
It is, SQLite’s docs are great and explain how it works in great detail.
Ah, a connoisseur of the semi-automatic foot-gun, I see. Or maybe the foot-RPG?
> It is, SQLite’s docs are great and explain how it works in great detail.
But not the “and when creating a table that way.” part?