In this lesson you connect to the consolidated database, create the CustomerProducts table, and alter the Customers table to include regional information.
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 Privileges section at the start of this tutorial: Tutorial: Using MobiLink with a SQL Anywhere 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 at another database.
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 Customers table, the primary key consists of the ID column. Any value inserted into the remote Customers table must have a unique customer ID number (the Region value is always the same). This practice ensures uniqueness in each remote Customers table. The primary key in the consolidated Customers table prevents conflicts if multiple salespeople upload data. Each upload from a region is unique from another region because their Region values are different.
Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
Click Connections » Connect With SQL Anywhere 16.
Perform the following tasks in the Connect window:
In the Action dropdown list, choose Connect With An ODBC Data Source.
In the ODBC Data Source name field, type SQL Anywhere 16 Demo.
Connect to your consolidated database in Interactive SQL.
At a command prompt, run the following command:
dbisql -c "DSN=SQL Anywhere 16 Demo"
In Interactive SQL, execute the following statements to create and insert data in the CustomerProducts table:
CREATE TABLE CustomerProducts (ID int default AUTOINCREMENT PRIMARY KEY, SalesOrderID int NOT NULL, CustomerID int NOT NULL, ProductID int); INSERT INTO CustomerProducts (SalesOrderID,CustomerID,ProductID) SELECT SalesOrders.ID, SalesOrders.CustomerID, SalesOrderItems.ProductID FROM SalesOrders, SalesOrderItems WHERE SalesOrders.ID = SalesOrderItems.ID;
In Interactive SQL, execute the following statements to add regional information for each customer to the Customers table:
ALTER TABLE Customers ADD Region VARCHAR(255); UPDATE Customers SET Region = (SELECT TOP 1 SalesOrders.Region FROM SalesOrders WHERE Customers.ID = SalesOrders.CustomerID ORDER BY Region); COMMIT;
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|