DataCompare: Final Sync Script generates a Key Violation
mikehkkk
Posts: 1 New member
Hello,
My final sql sync script includes deletes and inserts into a specific table that doesn't correctly deal with a key on that table. The key involved comprises a field that has a relationship with another field IN THE SAME TABLE. Let's call the first field of the relationship ID. The second field (again on same table) is PARENT_ID.
(ID is the primary key field to)
Delete Statements (sync creates these in the wrong order):
Three rows to be deleted, the FIRST row has an ID value that the two successive rows have a relationship with: Those rows' PARENT_ID values refer back to the ID field on first row. Key violation occurs as the row those second two rows depend on tries to get deleted first. The correction to this: moving that first row deletion into the last (third) position. This allows the two "child" rows to be deleted first, and this works.
INSERT Statements (sync also creates these in the wrong order);
Same three throws essentially, but this time the final script attempts to insert the two rows with PARENT_ID values referring back to an ID value of another row inserted first. Problem is, this "parent" row is not created first. (The correction: Moving the third/last INSERT statement to the beginning of the three rows inserts, puts in place the ID value that the successive two rows' PARENT_ID values depend upon).
I don't understand why the sync utility is creating a script without this essential "coordination"... recognizing this key relationship.
Anyone have any ideas?
My final sql sync script includes deletes and inserts into a specific table that doesn't correctly deal with a key on that table. The key involved comprises a field that has a relationship with another field IN THE SAME TABLE. Let's call the first field of the relationship ID. The second field (again on same table) is PARENT_ID.
(ID is the primary key field to)
Delete Statements (sync creates these in the wrong order):
Three rows to be deleted, the FIRST row has an ID value that the two successive rows have a relationship with: Those rows' PARENT_ID values refer back to the ID field on first row. Key violation occurs as the row those second two rows depend on tries to get deleted first. The correction to this: moving that first row deletion into the last (third) position. This allows the two "child" rows to be deleted first, and this works.
INSERT Statements (sync also creates these in the wrong order);
Same three throws essentially, but this time the final script attempts to insert the two rows with PARENT_ID values referring back to an ID value of another row inserted first. Problem is, this "parent" row is not created first. (The correction: Moving the third/last INSERT statement to the beginning of the three rows inserts, puts in place the ID value that the successive two rows' PARENT_ID values depend upon).
I don't understand why the sync utility is creating a script without this essential "coordination"... recognizing this key relationship.
Anyone have any ideas?
Tagged:
Answers
Thanks for reaching out to us regarding this.
Please can you confirm what version of SQL Compare you are using?
Also, just so that I can ensure I produce the closer reproduction of this issue, are you possibly able to provide the script for the scenario in question? If you are not comfortable with doing this via the forum I'd be happy to reach out to you directly from the ticket we have created for this.
Dan Jary | Redgate Software
Have you visited our Help Center?