@Erik_Meyer Hi Erik, one of the main reasons why AppSheet does not support auto-incremented keys in databases is because AppSheet apps support delayed sync and offline mode. To better understand this, consider this scenario:
There are 2 users A and B using the same app. Due to the demands of their jobs, both of these users need to add large numbers of rows to the app without syncing for long periods of time (either by delaying sync, or in offline mode). During the time period before the rows can be written back to the database, the app still needs to be able to uniquely identify the newly added rows so that the users can use features such as filtering, row updates, row deletes, reference expressions, etc. If the only way for the app to assign new keys to the newly added rows is to sync so that the keys can be auto-incremented by the database, these features won’t work. If on the other hand, the app attempts to auto-increment new keys while offline, then very soon the copy of the app used by user A and the copy of the app used by user B will generate the same keys, which will result in duplicate keys in the database when the 2 users finally sync.
Apart from the reason that I explain above, even if you don’t use AppSheet with your database, using auto-incremented keys as your primary keys is actually not a good practice. There are a number of reasons, but below are 2 big ones:
1/ Scaling: using auto-incremented keys prevents you from sharding/partitioning your database. The moment you divide your database into multiple shards/partitions in order to accommodate larger amounts of data or to service larger numbers of requests, your shards will sooner or later end up with duplicate keys. This is actually very similar to the situation between users A and B that I described above. If you need to merge data across the shards for analytics or aggregation, you’ll run into troubles.
2/ Security: auto-incremented keys reveal information about your data. In the past, when databases were not online, this was not a big issue. However, databases are now frequently used by online applications. What’s worse, the rise of APIs means that the serial auto-incremented keys that your database generated might end up in an API URL somewhere, exposed and visible to the public. Just by looking at these keys in the URLs, an attacker can guess the size of your database and more. And since the keys are probably serial, the attacker can easily use a script to send repeated requests to serially generated API URLs to enumerate and scrape the entries in your database.
I’m not a historian, but my bet is that auto-increment keys were invented before the Internet. With the Internet comes scale, complexity (offline mode, concurrent access, etc.), and more threats, so auto-increment keys are fast becoming obsolete. As such, I suggest using auto-incremented keys only for prototyping or experimentation. For production databases, unique primary keys, which can be generated using the built-in UniqueID() expression in AppSheet, are the better options.