Lesson 3: Adding unique keys

This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.

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.

Add unique primary keys to the consolidated database

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.

Note

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. This means that 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.

Proceed to Lesson 4: Connecting with MobiLink.