How do you prevent SharePoint from resetting primary key numbers - Backend on Two sites must match

[tj7933]

I have an MS Access 2016 database storing its back end on a SharePoint online site for one or our teams.

Another team within our agency wants to use the same database for their site. I must upload the same database skeleton and lookup tables and their information to the new site.

The development team leader (cannot be contradicted) insists that both sites' have the same primary keys for all lookup items.

Whenever I upload a table from a backend in Access, SharePoint resets the primary keys starting with the number one and continuing sequentially until finished. If any changes were made since the tables were uploaded, when those tables are localized and then uploaded to a new site, SharePoint changes the primary keys to remove the gaps in numbers that exist because of deleted entries or changes.

Is there a way to bypass this issue? Do I need to create a redundant primary key field that will not be changed when the table is uploaded to the new site? Creating a redundant key means that all the tables on the old site will need to be updated to include this field, and the entire front end (queries and forms) will need to be revised for both sites to use the same interface.

Please tell me there is an easier way to ensure the primary keys are the same.

Thanks Tammy

[#2]

@tj7933 

"Is there a way to bypass this issue?"

Not that I know of, not without custom hoops and special jumping shoes. If you really want to jump through hoops to satisfy that team leader, you might be able to figure out a complicated plan, but those special jumping shoes can be expensive and hard to maintain. I doubt it's worthwhile.

Here's why. Primary Keys are NOT, NOT, NOT for public consumption. The Team Leader insisting on matching them up across two different applications is simply asking for more than is reasonable.

You could add that additional field, not a Primary Key per se, but one with a Unique Index on it. You could use it to coordinate your two applications. 

Users should not need to see, or even be aware of, the existence of Primary Keys in a table. They enforce uniqueness on the values in a table and they provide a way to reinforce referential integrity between related tables. Hide them from this Team Leader and let them see your surrogate, Uniquely Indexed, Matching field.

[#3]

Alternatively, create a separate copy of the Lookup table(s) that is shared by the two teams.

[#4]

Thanks for your reply, that is what I was afraid of. The other DB associated with our team created TextBased primary keys as well as using the numerical based ID keys. I really did not want to create that redundancy within the program. Tammy

[#5]

SharePoint will ONLY use its own Primary Keys for relationships between tables. And, to enforce Referential Integrity between lists (tables as they are linked in Access), SharePoint can only use the Lookup fields using its own Primary Keys. I don't know what those text based primary keys really are, but I doubt they are actually working as Primary Keys within the application,

[#6]

I find the text IDs redundant. That database uses vba programming to update both the pkID (present in the TITLE field) and a secondary TxtID when changes are made or new items are created. I wanted to avoid this redundancy and tried to explain my concerns, but I was cut off in the conversation. Turns out I am not a very good communicator.