Guaranteed unique id's between multiple offline clients?

Hi everyone, I am working on a database application that relies on BKeeney Active Record as its ORM SQLite as the local database and PostgreSQL as my remote server database.
I am running into a bit of an issue with the primary key though. Active Record requires you to use an integer as the primary key which normally wouldn’t be a problem since you can just use an auto incrementing id field. Since my application syncs to an external database, and there are multiple users syncing to this external database, an auto-incrementing field doesn’t work. Putting the auto incrementing on the external database doesn’t work either, because having offline capabilities is a primary goal of the application.
I would just use a local auto-incrementing field paired with a unique client id received from the external database. But in the future I would like to allow for partial syncs from the external databse, so users can pull down all the information from a specific year, month, etc.

So that brings me to my real question. Is there a way to create an integer that is guaranteed to be unique / is there some other way to ensure that I don’t have overlapping id’s?

Sean O.
Northern Sun Productions

  • Introduce a candidate key of some type such as a GUID to sit beside the id making it unique for the syncing

Depending on the number of unique locations, you could add another digit to the auto-increment logic.
Location one add “1”, location two add “2”, etc
Unique IDs for one would be 11, 21, 31
for two this would be 12, 22, 32
If you have more than 9 locations, add two digits,
For more than 999, add three
At some stage this probably becomes too cumbersome, I presume, but works well for smaller number of users.