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 want 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 includes the SalesOrders table:

CREATE PUBLICATION PubSales (
   TABLE Customers,
   TABLE SalesOrders,
   TABLE SalesOrderItems,
   TABLE Products
);

The SalesOrders table has a foreign key to the Employees table. The ID of the sales rep is a foreign key in the SalesOrders table referencing the primary key of the Employees table. However, the Employees 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 SalesOrders 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 SalesRepresentative column of the SalesOrders table, and if this happens the INSERT will fail at the consolidated database. To avoid this problem, you can include the Employees table (or at least its primary key) in the publication.