I have an app which connects with my web service to download and insert in to a sqlite db.
The app also has the facility to insert user created entries which get uploaded to the web service and updated if they exist or inserted if they don't.
I'm looking for a foolproof way of ensuring that there won't be any conflict between primary key id (int) values.
One idea is to set the web service to begin incrementing at an arbitrary high number say one billion and the apps to start from 1. This approach scares me a little as even though it's unlikely that the one billion value will be reached by the entries the apps create it's still a possibility and also throws up potential problems when carrying out future maintenance.
Another simpler solution would be to renumber any existing ids on the app should a conflict occur.
Does anybody have any experiences of similar problems and would like to share their solutions?