Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » UltraLite - Database Management and Developer Guide » UltraLite as a MobiLink client » UltraLite clients » UltraLite client synchronization design » Table order in UltraLite

Avoiding synchronization issues with foreign key cycles

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 database 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, use the CHECK ON COMMIT clause on one of the foreign keys. 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.