Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » MobiLink - Getting Started » MobiLink tutorials » Tutorial: Using MobiLink with an Adaptive Server Enterprise consolidated database

 

Lesson 3: Adding unique keys to the consolidated database

In this lesson, unique primary keys are added to the consolidated 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 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.

 Task
  1. 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.

  2. 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'
  3. 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
  4. 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, 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.

Results

Rows that are not unique are dropped and unique primary keys are added to the consolidated database.

 See also