User's Manual

Chapter 7. SQL Remote Design for Adaptive Server Anywhere
SQL Remote resolves conflicts; you may just want to report the conflicts by
storing them in a table. In this way, you can look at the conflict table to see
what, if any, conflicts have occurred, and if necessary take action to resolve
the conflicts.
Designing to avoid referential integrity errors
The tables in a relational database are related through foreign key references.
The referential integrity constraints applied as a consequence of these
references ensure that the database remains consistent. If you wish to
replicate only a part of a database, there are potential problems with the
referential integrity of the replicated database.
By paying attention to referential integrity issues while designing
publications you can avoid these problems. This section describes some of
the more common integrity problems and suggests ways to avoid them.
Unreplicated referenced
table errors
The sales publication described in “Publishing whole tables” on page 93
includes the sales_order table:
CREATE PUBLICATION pub_sales (
TABLE customer,
TABLE sales_order,
TABLE sales_order_items,
TABLE product
)
The sales_order table has a foreign key to the employee table. The id of the
sales rep is a foreign key in the sales_order table referencing the primary
key of the employee table. However, the employee table is not included in
the publication.
If the publication is created in this manner, new sales orders would fail to
replicate unless the remote database has the foreign key reference removed
from the
sales_order
table.
If you use the extraction utility to create the remote databases, the foreign
key reference is automatically excluded from the remote database, and this
problem is avoided. However, there is no constraint in the database to
prevent an invalid value from being inserted into the sales_rep_id column of
the sales_order table, and if this happens the INSERT will fail at the
consolidated database. To avoid this problem, you can include the employee
table (or at least its primary key) in the publication.
Designing triggers to avoid errors
Actions performed by triggers are not replicated: triggers that exist at one
database in a SQL Remote setup are assumed by the replication procedure to
127