Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to Resolve foreign key referencial integrity constraints violation

  1. #1

    Default How to Resolve foreign key referencial integrity constraints violation

    Hi All,

    I am facing foreign key referencial integrity constraints violation.My requirement is as follows:-
    1.I have a table name for eg:temp in the source database(Oracle).
    2.From Source database table (temp) breack the row in two parts and,migrate the data in two table(temp1,temp2) of target database(Mysql) based on sequence generated in transformation steps.
    3.Sequence generated column(id) of table temp1 is foreign key reference with table temp2 of column id .


    My Approach
    ===============

    1.Taken a input from the source database and write the sql script for column selection.
    2. Take add sequence step from the transformation and connect through the HOB from input table.
    3.From add sequence step i have created two table output for table temp1 and temp2 of mysql database .
    4.Connect both the table output through HOB with distribute option fromm add sequence steps.
    5.Generate column mapping step for each output step.

    Problem:-Error generating foreign key constraints violation for table temp2.But the transformation runs successfully if run again and again without any changes in transformation.

    Kindly share the solution ,so that i can run the transformation successfully in single try.

    Regards,
    Santosh

  2. #2
    Join Date
    Dec 2011
    Posts
    124

    Default

    Hi,

    Seems to be whatever you have defined foreign key constraint to the master table data is not loaded properly. please check once how much volume of master data is aviliable in source database and how much volume of data is aviliable in target table... then you can get an idea where you have missed records data.

    child table will raise foreign key constraint issue, if at all master data is not loaded properly...

    Thank you

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    In your scenario, you can't rely on Kettle and MySQL to commit a primary key in table #1, before some foreign key constraint from table #2 gets checked.

    Either you write table #1 before table #2 or you must disable FK checks on the output connection - advanced tab:

    Code:
    SET foreign_key_checks = 0;
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Remember that your two table output steps will run in parallel with different DB connections if you don't set your transformation to be transactional.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.