View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002244 | SymmetricDS | Bug | public | 2015-03-30 15:58 | 2022-08-02 18:58 |
| Reporter | ratishkr | Assigned To | elong | ||
| Priority | high | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.7.0 | ||||
| Target Version | 3.10.0 | Fixed in Version | 3.10.0 | ||
| Summary | 0002244: Self- Referential Foreign Keys result in Contraint violation when loading data | ||||
| Description | I have couple of tables that have self referential integrity contraints, like the usual scenario, where the manager of the employee is also an employee on the same table, and the manager ID column will have a Self referential key on the Employee Number column. Since the data load can happen in any particular order, if the manager record gets loaded first and then the employee record gets in, there would not be any issues. But if the employee whose manager record is not inserted yet, goes in first, it would fail with constraint violation. The self referential key must be added to the table either towards the end after the Data gets loaded, or should be disabled and then enabled back after loading data. | ||||
| Steps To Reproduce | Create a simple table: 'Employee Number', 'Employee Name', 'Manager Number'. Make Employee Number as the primary key, and add a self referential foreign constraint on the Manager Number column referring the 'Employee Number' column. Insert the following records: <1, EMP_A, null> <2, EMP_B, 1> <3, EMP_C, 1> <4, EMP_D, 2> Now extract the records in XML format and try loading it to a new target in another database. If the records in the export gets exported into the file in the exact order we did above, the load will not have any issues. To make it not work manually, you can change the order of the records in the XML file, such that the child gets load first before the parent employee record. | ||||
| Tags | dbimport/dbexport | ||||
|
|
Can we get admins attention on this matter. Is there a simple workaround this? |
|
|
There are properties to run sql scripts prior to and after and after an initial load. The are called initial.load.before.sql and initial.load.after.sql. You can disable fk and reenable them in these scripts. Another approach would be to break up your employee initial load across two trigger routers with different initial load select criteria. In order to do that you would have to have a "dead" employee trigger (one that had sync_on_* all set to 0). |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-03-30 15:58 | ratishkr | New Issue | |
| 2017-01-22 09:44 | Zulla | Note Added: 0000957 | |
| 2017-01-22 15:38 | chenson | Note Added: 0000958 | |
| 2019-04-23 16:53 | elong | Tag Attached: dbimport/dbexport | |
| 2022-08-02 18:57 | elong | Relationship added | related to 0003921 |
| 2022-08-02 18:58 | elong | Assigned To | => elong |
| 2022-08-02 18:58 | elong | Status | new => closed |
| 2022-08-02 18:58 | elong | Resolution | open => fixed |
| 2022-08-02 18:58 | elong | Fixed in Version | => 3.10.0 |