In this lesson, unique primary keys are added to the consolidated database.
This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.
This lesson assumes you have the roles and privileges listed in the Permissions and privileges section at the start of this tutorial: Tutorial: Using MobiLink with an Adaptive Server Enterprise consolidated database.
Context and remarks
In a synchronization system, the primary key of a table is the only way to uniquely identify a row in different databases and the only way to detect conflicts. Every table that is being mobilized must have a primary key. The primary key must never be updated. You must also guarantee that a primary key value inserted at one database is not inserted in another database.
There are several ways to generate unique primary keys. For simplicity, the method of composite primary keys is used in this tutorial. This method creates primary keys with multiple columns that are unique across the consolidated and remote databases.
Connect to the pubs2 database as sa, using isql in Adaptive Server Enterprise. At a command prompt, run the following command, all on one line:
isql -U sa -P your-password-for-sa-account -D pubs2
If you are accessing Adaptive Server Enterprise remotely, use the -S option to specify the server name.
The following rows are not unique based on the composite primary key created for the salesdetail table. For simplicity, drop the rows by executing the following statements:
DELETE FROM salesdetail WHERE stor_id = '5023' AND ord_num = 'NF-123-ADS-642-9G3' AND title_id = 'PC8888' DELETE FROM salesdetail WHERE stor_id = '5023' AND ord_num = 'ZS-645-CAT-415-1B2' AND title_id = 'BU2075'
The following indexes interfere with the creation of primary keys in a previous step. To drop the indexes, execute the following statements:
DROP INDEX authors.auidind DROP INDEX titleauthor.taind DROP INDEX titles.titleidind DROP INDEX sales.salesind
Add unique primary keys by executing the following statements:
ALTER TABLE au_pix ADD PRIMARY KEY (au_id) ALTER TABLE authors ADD PRIMARY KEY (au_id) ALTER TABLE titleauthor ADD PRIMARY KEY (au_id, title_id) ALTER TABLE titles ADD PRIMARY KEY (title_id) ALTER TABLE discounts ADD PRIMARY KEY (discounttype) ALTER TABLE stores ADD PRIMARY KEY (stor_id) ALTER TABLE sales ADD PRIMARY KEY (stor_id, ord_num) ALTER TABLE salesdetail ADD PRIMARY KEY (stor_id, ord_num, title_id)
After executing these statements, the MobiLink server connects to the consolidated database and sets up synchronization for any number of remote databases.
It is possible to synchronize data with consolidated databases that do not have primary keys. However, you must write you own synchronization events that act on shadow tables that are designed to identify rows uniquely in other tables.
In a later lesson, the remote schema is created from the consolidated schema, so the remote schema has the same primary keys as the consolidated schema.
Columns were specifically chosen to ensure unique primary keys for all databases. For the sales table, the primary key consists of the stor_id and ord_num columns. Any value inserted into the remote sales table must have a unique order number (the stor_id value is always the same). This practice ensures uniqueness in each remote sales table. The primary key in the consolidated sales table prevents conflicts if multiple stores upload data. Each upload from one store is unique to another store because their stor_id values are different.
For the salesdetail table, the primary key consists of the stor_id, ord_num, and title_id columns. There may be multiple book titles in an order. For the remote sales tables, rows may have the same values for stor_id and ord_num, but they must have different title_id values. This configuration ensures uniqueness in each remote salesdetail table. Similar to the sales table, each upload to the consolidated database from a store is unique to another store because their stor_id values are different.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|