October 30, 2007 at 7:11 am
I am trying to import data into multiple tables with a store proc and it gives FK constraint error on the destination DB, I will be importing data every day, how can I handle this kind of errors?
October 30, 2007 at 7:36 am
Foreign key errors mean that you are trying to insert children into a "parent-child" set of tables.
For example - if you had InvoiceMain and and InvoiceDetail tables (the invoice detail table holds the lineitems on a given invoice), then trying to insert detail records that don't match up to a Main invoice record would error out.
So - how do you fix it? Well it depends on what you process actually does. If your process is inserting into a series of tables, it may be that you simply need to change the order of the inserts, so that the Parent records are present when the child records are being inserted.
If you're inserting into a single table (or you're not inserting into the parent), you now have a BUSINESS decision to handle first before fixing the issue. Do you "force" the records in, or do they get carve off somewhere else for handling, or do they just get pitched because they're invalid/not relevant/etc...?
If you need to "force" them in, then you need to revise your process to CREATE the parent record, or update the child records to correct foreign key values. If you need to carve them out, then you need to add something to your WHERE clause to exclude those with invalid foreign keys, and add a step to insert them into some other table. If you're pitching them, then just add the extra clause to the WHERE statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply