SQL Remote User's Guide
SQL Remote Design for Adaptive Server Enterprise
Managing conflicts
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.
Referential integrity errors stop replicationIf a remote database receives a message that includes a statement that cannot be executed because of referential integrity constraints, no further messages can be applied to the database (because they come after a message that has not yet been applied), including passthrough statements, which would sit in the message queue. |
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.
Consider the following SalesRepData publication:
exec sp_add_remote_table 'SalesRep' exec sp_create_publication 'SalesRepData' exec sp_add_article 'SalesRepData', 'SalesRep' go
If the SalesRep table had a foreign key to another table (say, Employee) that was not included in the publication, inserts or updates to SalesRep would fail to replicate unless the remote database had the foreign key reference removed.
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 rep_id column of the SalesRep table, and if this happens the INSERT will fail at the consolidated database. To avoid this problem, you could include the Employee table (or at least its primary key) in the publication.