Primary Keys in a Distributed Database Environment
This tutorial shows you how to set up a synchronization environment with unique keys across all remote databases. In a distributed database environment, typically you have one consolidated database, and any number of remote databases. These remote databases may be able to enter new database records and synchronize these new records up to the consolidated database. In this scenario, a strategy must be employed to ensure the same primary key value is not entered in the same table on two different remote databases, which would result in a conflict at the consolidated database.
This tutorial assumes you have a consolidated database (either SQL Anywhere, Sybase Adaptive Server Enterprise, Oracle, or Microsoft SQL Server) synchronized with any number of SQL Anywhere remote databases. It shows you how to develop and implement a strategy to assign a unique set of primary keys to the consolidated database and each remote database. The strategy you develop utilizes a method specific to whatever consolidated database you decide to use, and global autoincrementing fields on the remote databases.
There are several ways to achieve unique keys across remotes. SQL Anywhere offers global autoincrementing fields as one possibility and this is the focus of this tutorial. Other approaches are discussed in detail in the SQL Anywhere documentation.