View Issue Details

IDProjectCategoryView StatusLast Update
0002244SymmetricDSBugpublic2022-08-02 18:58
Reporterratishkr Assigned Toelong  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.7.0 
Target Version3.10.0Fixed in Version3.10.0 
Summary0002244: Self- Referential Foreign Keys result in Contraint violation when loading data
DescriptionI 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 ReproduceCreate 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.
Tagsdbimport/dbexport

Relationships

related to 0003921 closedelong Parameter to disable recursive querying of each level in table with self-referencing foreign key 

Activities

Zulla

2017-01-22 09:44

reporter   ~0000957

Can we get admins attention on this matter.
Is there a simple workaround this?

chenson

2017-01-22 15:38

administrator   ~0000958

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).

Issue History

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