Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere
SQLRemote resolves conflicts; you may just want to report the conflicts by
storingthem in a table. Inthis way, you can look at the conflict table to see
what,if any, conflicts have occurred, and if necessary take action to resolve
theconflicts.
Designing to avoid referential integrity errors
Thetables in a relational database are related through foreign key references.
Thereferential integrity constraints applied as a consequence of these
referencesensure that the database remains consistent. Ifyou wish to
replicateonly a part of a database, there are potential problems with the
referentialintegrity of the replicated database.
Bypaying attention to referential integrity issues while designing
publicationsyou can avoid these problems. Thissection describes some of
themore common integrity problems and suggests ways to avoid them.
Unreplicatedreferenced
tableerrors Thesales publication described in “Publishing whole tables” on page 93
includesthe sales_order table:
CREATE PUBLICATION pub_sales (
TABLE customer,
TABLE sales_order,
TABLE sales_order_items,
TABLE product
)
Thesales_order table has a foreign key to the employee table. Theid of the
salesrep is a foreign key in the sales_order table referencing the primary
keyof the employee table. However,the employee table is not included in
thepublication.
Ifthe publication is created in this manner, new sales orders would fail to
replicateunless the remote database has the foreign key reference removed
fromthe sales_order table.
Ifyou use the extraction utility to create the remote databases, the foreign
keyreference is automatically excluded from the remote database, and this
problemis avoided. However,there is no constraint in the database to
preventan invalid value from being inserted into the sales_rep_id column of
thesales_order table, and if this happens the INSERT will fail at the
consolidateddatabase. Toavoid this problem, you can include the employee
table(or at least its primary key) in the publication.
Designing triggers to avoid errors
Actionsperformed by triggers are not replicated: triggersthat exist at one
databasein a SQL Remote setup are assumed by the replication procedure to
127