I’ve seen this over and over for the last 30 years, people let the database set the ID or Primary Key of a table from the database, at first glance this sounds simple and everyone knows you should let the database do the heavy lifting, with a numeric “Sequence” number you need to let the database do the work since there may be multiple applications or threads creating new records in the table. DON”T DO IT!
First, if and when you need to merge two databases that now have the same Primary Key ID values for the same table, your screwed. You have to come up with a scheme to change the ID’s, maybe adding 10,000 to each ID, what if you have more than 10,000 rows? The you also have to update all children records, maybe not that easy if you have constraints defined in the database.
Second, if you expose your data as an API then sooner or later that will happen, you have just opened up your system to what hackers love to see. Let me explain:
I can login and get access to my information, maybe my purchases as follows:
GET https://secureserver.com/puchases/123
Simple right? I get the purchases for user ID 123, but what if you make another call:
