Referential integrity errors

The tables in a relational database are often related through foreign key references. As a result, referential integrity constraints ensure that the database remains consistent.

When you replicate only a part of a database, you must ensure that the replicated database still has referential integrity.

You want to avoid unreplicated referenced table errors. Your remote databases should not contain foreign keys that point to unreplicated tables.

For example, in a consolidated database the SalesOrders table has a foreign key to the Employees table. SalesOrders.SalesRepresentative is the foreign key that references the primary key, Employees.EmployeeID.

Entity-relationship diagram of the example database.

A publication, PubSales, is created that excludes the Employees table, but includes the entire SalesOrder table.

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

A remote user, Rep1, subscribes to the PubSales publication. Then, you extract Rep1 from the consolidated database and try to create a database for Rep1. However, the database creation fails because Rep1 is missing the Employees table. To avoid this problem, you can:

Remove the foreign key reference To exclude foreign key references, specify the -xf option when using the Extraction utility (dbxtract).
However, if you remove the foreign key reference from the remote database, then there is no constraint in the remote database to prevent an invalid value from being inserted into the SalesRepresentative column of the SalesOrders table.

If an invalid value is inserted in the SalesRepresentative column at the remote database, the replicated INSERT statement fails on the consolidated database.

Include the missing table in the publication Include the Employees table (or at least its primary key) in the publication. For example:

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

Referential integrity errors

See also