Lesson 3: Adding unique keys to the consolidated database

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.

Prerequisites

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 Oracle Database 10g.

Context and remarks

There are several ways of generating 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.

Task

At a command prompt, run the following command:
sqlplus SYS/your password for sys as SYSDBA

Values added to the SALES_REP_ID must exist in the HR.EMPLOYEES table. The ORDERS_SALES_REP_FK foreign key enforces this rule. Execute the following statement to drop the foreign key:

ALTER TABLE OE.ORDERS 
DROP CONSTRAINT ORDERS_SALES_REP_FK;

The SALES_REP_ID column cannot be added as a primary key because it contains null values. For this tutorial, replace the null values with a value of 1. Execute the following statement:

UPDATE OE.ORDERS
SET SALES_REP_ID = 1
WHERE SALES_REP_ID IS NULL;

The ORDER_ID column is the current primary key of the ORDERS table. To drop the current primary key, execute the following statement:
ALTER TABLE OE.ORDERS DROP PRIMARY KEY CASCADE;

The composite primary key consists of the SALES_REP_ID column and the ORDER_ID column. To add the composite primary key, execute the following statement:

ALTER TABLE OE.ORDERS
ADD CONSTRAINT salesrep_order_pk PRIMARY KEY (sales_rep_id, order_id);

Results

After executing these statements, the MobiLink server connects to the consolidated database and sets up synchronization for any number of remote databases.

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 ORDERS table, the primary key consists of the SALES_REP_ID and ORDER_ID columns. Any value inserted into the remote sales table must have an unique order number (the SALES_REP_ID value is always the same). This practice ensures uniqueness in each remote ORDERS table. The primary key in the consolidated ORDERS table prevents conflicts if multiple salespeople upload data. Each upload from a salesperson is unique to another salesperson because their SALES_REP_ID values are different.

Next

Proceed to Lesson 4: Connecting with MobiLink.

Discuss this page in DocCommentXchange.