The approach I'm proposing is just to insert via select. The first script you propose, inserting into table1_temp, is almost what you need. You need to distinguish the uploads of different remote databases, so you need the remote id as a column:

INSERT INTO table1_temp (remote_id, col1, col2) VALUES( {ml s.remote_id}, {ml r."col1"}, {ml r."col2"} )

The actual syntax for the end_upload script depends on your consolidated database type, but it could be something like:

INSERT INTO table1 ( col1, col2 ) SELECT col1, col2 FROM table1_temp WHERE remote_id = {ml s.remote_id}

You'll also need to delete the remote_id's table1_temp rows at the end of the end_upload script.

• Russ

But there's more to it than that. Just replacing the existing insert scripts with inserts into temp tables and then inserting into the base table later in the synchronization will not solve the problem. Once the entire upload is available, you'll need to sort out how to insert the data into the consolidated without causing a foreign key violation.

IMHO, we are missing a lot of information here to properly answer the question and/or provide a solution.

To start with :

1. Which RDBMS are you using as the consolidated database?
2. Are the table definitions and foreign keys in your remote database the same as the table definition and foreign keys in the consolidated database?

Reg

I'd like to add that the mentioned warning about cyclic relationships should not appear in the simple 1:n FK relationship between two tables as described in the original question. So I guess we need to know more about the details of the problematic tables and the noticed RI violations.

If the tables definitions and foreign keys are all the same between the remote and consolidated, and given that the data at the remote database is a subset (or a complete copy) of the data at the consolidated, there should be no reason for a cycle of foreign keys to result in a foreign key violation when applying the upload in the MobiLink Server.

Because you have a cycle of foreign keys at the remote site, that means that the foreign keys defined must use the WAIT FOR COMMIT option, or it would be impossible to ever insert rows into the tables in questions. You've stated the foreign keys are the same at the consolidated, so those foreign keys are also defined with the WAIT FOR COMMIT option at the consolidated. It's possible that you've also set the wait_for_commit database option at both sites as opposed to using the WAIT FOR COMMIT option in the foreign key definitions. The upload is all applied in a single transaction, so no RI checking occurs until the commit, when all the data from the remote has been uploaded, and there should be no RI issues, since none existed at the remote database.

Given this information, there are three possibilities :

1) The foreign key violations are occurring simply because you have a row in a parent table that exists at the remote site that does not exist in the consolidated database. This theory would also explain why you are seeing the problem in your test environment, and not in production.

2) The tables definitions and foreign key definitions are NOT exactly the same at the consolidated and remote. If they differ, it may only be with the WAIT FOR COMMIT option on the foreign key constraint.

3) You have set the "wait_for_commit" database option at the remote site, but not at the consolidated.

I would suggest going back to your original error with this information and attempting to figure out which of the options above may be the source of your problem. If you can't figure it out, we'll probably need to see the dbmlsync output log (with -v+) and the MobiLink Server output log (also with -v+) from a failed synchronization to figure out what's going on.

Reg