Not sure if I understand the question correctly, but if you're going to insert data on several separate locations you basically have two options concerning the primary key:
- Ensure that the primary key column or combination of columns is unique across all locations
- Or you centralize the key management
The problem with the latter is that you need to have a connection from the remote server to the central server when a new key value is needed. If the connection is not reliable, then this probably is not an option.
If you let each location to add records independently then there are few simple ways to ensure that the keys are unique
- Use two-column key. One column to uniquely identify the record inside the server, another column to define the server where the insertion happened. This would be fetched from configuration upon insert
- Use GUID for the primary key. It's unique across servers by nature
- If your current key is an identity and you want to stick with it (which I think is the worst option) use the possibility to define range for the identity key. However, you need to roughly know how many records will be inserted in each server in order to be able to define a range that does not overlap..
But you also need to consider that the surrogate key is not the only key to worry about. If you have natural keys, and hopefully you do, you need to decide what are you going to do with possible duplicates with natural keys.
If you're going to allow them, then you need the location, or something similar, in the natural key. If you don't allow them then you need take care of them once the data is merged. Also you need to take care of the child records if duplicates are merged.
And of course INSERT is not the only operation that changes data, you also have UPDATE's and DELETE's :)
Submit an article or tip