Table order is particularly important for UltraLite databases that use foreign key cycles. A cycle occurs when you link a series of tables together such that a circle is formed. However, due to complexities that arise when cycles between the consolidated database and the UltraLite remote differ, foreign key cycles are not recommended.

With foreign key cycles, you should order your tables so that operations for a primary table come before the associated foreign table. A Table Order parameter ensures that the insert in the foreign table will have its foreign key referential integrity constraint satisfied (likewise for other operations like delete).

In addition to table ordering, another method you can use to avoid synchronization issues is to postpone the checking of referential integrity until the transaction is committed. If your consolidated database is a SQL Anywhere database, set one of the foreign keys to check on commit. This ensures that foreign key referential integrity is checked during the commit phase rather than when the operation is initiated. For example:

CREATE TABLE c (
    id INTEGER NOT NULL PRIMARY KEY,
    c_pk INTEGER NOT NULL
);
CREATE TABLE p (
    pk INTEGER NOT NULL PRIMARY KEY,
    c_id INTEGER NOT NULL,
    FOREIGN KEY p_to_c (c_id) REFERENCES c(id)
);
ALTER TABLE c
   ADD FOREIGN KEY c_to_p (c_pk)
   REFERENCES p(pk)
   CHECK ON COMMIT;

If your consolidated database is from another database vendor, check to see if the database has similar methods of checking referential integrity. If so, you should implement this method. Otherwise, you must redesign table relationships to eliminate all foreign key cycles.

• Referential integrity and synchronization