how to discard rows of a text file which does not satisfies foreign key constraint and continue with

Hi All,

I am loading data to a child table from a text file. the text files
also contains data not referenced by parent key. while loading the data
if one row fails to satisfies the constraint everything is getting
rollback..

plz suggest me something.. which will help me to discard the
unsatisfied rows and continue with the rest..

My Query:
LOAD DATA CONCURRENT INFILE
'/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
(reg_id,func_address);

Error Msg:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
`CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))

Thanks
Devi.

Re: how to discard rows of a text file which does not satisfies foreignkey constraint and continue w

kutty wrote:
> Hi All,
>
> I am loading data to a child table from a text file. the text files
> also contains data not referenced by parent key. while loading the data
> if one row fails to satisfies the constraint everything is getting
> rollback..
>
> plz suggest me something.. which will help me to discard the
> unsatisfied rows and continue with the rest..
>
> My Query:
> LOAD DATA CONCURRENT INFILE
> '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
> INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
> (reg_id,func_address);
>
> Error Msg:
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
> `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
> REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
>
> Thanks
> Devi.
>
One approach is to load a temporary table which has no
constraints and then insert from the temporary to the child only
those rows where the foreign key constraint is satisfied.

Advantages are that minimal changes are required to your
existing load program and the insert can be handled with a
simple INSERT INTO child (SELECT * FROM temp WHERE constraints
are met); You may have to play around with the syntax -- I can
never remember if you need that parenthesis and/or an AS before
the SELECT.

HTH
Jerry

Re: how to discard rows of a text file which does not satisfies foreign key constraint and continue

Hi Jerry,

Thanx a lot..

but..I am loading a huge volume of data (min 2 GB). Two load operations
might increase the loading time. I dont want that to happen.. is there
any possible way to discard the rows which doent satify the constraint?
Oracle support this. It creates a discard file..

Thanks
Kutty

Jerry Gitomer wrote:
> kutty wrote:
> > Hi All,
> >
> > I am loading data to a child table from a text file. the text files
> > also contains data not referenced by parent key. while loading the data
> > if one row fails to satisfies the constraint everything is getting
> > rollback..
> >
> > plz suggest me something.. which will help me to discard the
> > unsatisfied rows and continue with the rest..
> >
> > My Query:
> > LOAD DATA CONCURRENT INFILE
> > '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
> > INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
> > (reg_id,func_address);
> >
> > Error Msg:
> > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> > constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
> > `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
> > REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
> >
> > Thanks
> > Devi.
> >
> One approach is to load a temporary table which has no
> constraints and then insert from the temporary to the child only
> those rows where the foreign key constraint is satisfied.
>
> Advantages are that minimal changes are required to your
> existing load program and the insert can be handled with a
> simple INSERT INTO child (SELECT * FROM temp WHERE constraints
> are met); You may have to play around with the syntax -- I can
> never remember if you need that parenthesis and/or an AS before
> the SELECT.
>
> HTH
> Jerry

Re: how to discard rows of a text file which does not satisfies foreign key constraint and continue

On 27 Sep 2006 05:04:45 -0700, in mailing.database.mysql "kutty"

<1159358685.701342.198560@b28g2000cwb.googlegroups.com> wrote:

>| Hi Jerry,
>|
>| Thanx a lot..
>|
>| but..I am loading a huge volume of data (min 2 GB). Two load operations
>| might increase the loading time.

It might also decrease the load time!
Loading imported data into a non-index, non constrained table is
pretty quick. Then taking that data and only moving the relevant data
is also pretty quick.

Loading imported data into a temp table has the benefits of:
if the import data is corrupted then it is easy to restart the process
without disturbing your live data.
further manipulation of the data can be carried out prior to transfer
to the live tables.
business rules can be applied to the data prior to going live
once the valid data has been processed you can then see what is left
behind and then make a value judgement if this data can be discarded
or incorporated into the live data.

>| I dont want that to happen.. is there
>| any possible way to discard the rows which doent satify the constraint?
>| Oracle support this. It creates a discard file..
>|
>| Thanks
>| Kutty
>|
>| Jerry Gitomer wrote:
>| > kutty wrote:
>| > > Hi All,
>| > >
>| > > I am loading data to a child table from a text file. the text files
>| > > also contains data not referenced by parent key. while loading the data
>| > > if one row fails to satisfies the constraint everything is getting
>| > > rollback..
>| > >
>| > > plz suggest me something.. which will help me to discard the
>| > > unsatisfied rows and continue with the rest..
>| > >
>| > > My Query:
>| > > LOAD DATA CONCURRENT INFILE
>| > > '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
>| > > INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
>| > > (reg_id,func_address);
>| > >
>| > > Error Msg:
>| > > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
>| > > constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
>| > > `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
>| > > REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
>| > >
>| > > Thanks
>| > > Devi.
>| > >
>| > One approach is to load a temporary table which has no
>| > constraints and then insert from the temporary to the child only
>| > those rows where the foreign key constraint is satisfied.
>| >
>| > Advantages are that minimal changes are required to your
>| > existing load program and the insert can be handled with a
>| > simple INSERT INTO child (SELECT * FROM temp WHERE constraints
>| > are met); You may have to play around with the syntax -- I can
>| > never remember if you need that parenthesis and/or an AS before
>| > the SELECT.
>| >
>| > HTH
>| > Jerry
------------------------------------------------------------ ---
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
------------------------------------------------------------ ---

Re: how to discard rows of a text file which does not satisfies foreign key constraint and continue

Hi

Thanks Jerry and Jeff for your valuable suggestion. I will try this
approach.

Thanks
Kutty.

Jeff North wrote:
> On 27 Sep 2006 05:04:45 -0700, in mailing.database.mysql "kutty"
>
> <1159358685.701342.198560@b28g2000cwb.googlegroups.com> wrote:
>
> >| Hi Jerry,
> >|
> >| Thanx a lot..
> >|
> >| but..I am loading a huge volume of data (min 2 GB). Two load operations
> >| might increase the loading time.
>
> It might also decrease the load time!
> Loading imported data into a non-index, non constrained table is
> pretty quick. Then taking that data and only moving the relevant data
> is also pretty quick.
>
> Loading imported data into a temp table has the benefits of:
> if the import data is corrupted then it is easy to restart the process
> without disturbing your live data.
> further manipulation of the data can be carried out prior to transfer
> to the live tables.
> business rules can be applied to the data prior to going live
> once the valid data has been processed you can then see what is left
> behind and then make a value judgement if this data can be discarded
> or incorporated into the live data.
>
> >| I dont want that to happen.. is there
> >| any possible way to discard the rows which doent satify the constraint?
> >| Oracle support this. It creates a discard file..
> >|
> >| Thanks
> >| Kutty
> >|
> >| Jerry Gitomer wrote:
> >| > kutty wrote:
> >| > > Hi All,
> >| > >
> >| > > I am loading data to a child table from a text file. the text files
> >| > > also contains data not referenced by parent key. while loading the data
> >| > > if one row fails to satisfies the constraint everything is getting
> >| > > rollback..
> >| > >
> >| > > plz suggest me something.. which will help me to discard the
> >| > > unsatisfied rows and continue with the rest..
> >| > >
> >| > > My Query:
> >| > > LOAD DATA CONCURRENT INFILE
> >| > > '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
> >| > > INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
> >| > > (reg_id,func_address);
> >| > >
> >| > > Error Msg:
> >| > > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> >| > > constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
> >| > > `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
> >| > > REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
> >| > >
> >| > > Thanks
> >| > > Devi.
> >| > >
> >| > One approach is to load a temporary table which has no
> >| > constraints and then insert from the temporary to the child only
> >| > those rows where the foreign key constraint is satisfied.
> >| >
> >| > Advantages are that minimal changes are required to your
> >| > existing load program and the insert can be handled with a
> >| > simple INSERT INTO child (SELECT * FROM temp WHERE constraints
> >| > are met); You may have to play around with the syntax -- I can
> >| > never remember if you need that parenthesis and/or an AS before
> >| > the SELECT.
> >| >
> >| > HTH
> >| > Jerry
> ------------------------------------------------------------ ---
> jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
> ------------------------------------------------------------ ---

Re: how to discard rows of a text file which does not satisfies foreign key constraint and continue

Hi All,

This is not working for me..
This approach is fine for smaller tables.But if i try to insert data
from the temporary table to the child table
Error occurs:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

can any one suggest me a solution?

Thanks
Kutty

kutty wrote:
> Hi
>
> Thanks Jerry and Jeff for your valuable suggestion. I will try this
> approach.
>
> Thanks
> Kutty.
>
> Jeff North wrote:
> > On 27 Sep 2006 05:04:45 -0700, in mailing.database.mysql "kutty"
> >
> > <1159358685.701342.198560@b28g2000cwb.googlegroups.com> wrote:
> >
> > >| Hi Jerry,
> > >|
> > >| Thanx a lot..
> > >|
> > >| but..I am loading a huge volume of data (min 2 GB). Two load operations
> > >| might increase the loading time.
> >
> > It might also decrease the load time!
> > Loading imported data into a non-index, non constrained table is
> > pretty quick. Then taking that data and only moving the relevant data
> > is also pretty quick.
> >
> > Loading imported data into a temp table has the benefits of:
> > if the import data is corrupted then it is easy to restart the process
> > without disturbing your live data.
> > further manipulation of the data can be carried out prior to transfer
> > to the live tables.
> > business rules can be applied to the data prior to going live
> > once the valid data has been processed you can then see what is left
> > behind and then make a value judgement if this data can be discarded
> > or incorporated into the live data.
> >
> > >| I dont want that to happen.. is there
> > >| any possible way to discard the rows which doent satify the constraint?
> > >| Oracle support this. It creates a discard file..
> > >|
> > >| Thanks
> > >| Kutty
> > >|
> > >| Jerry Gitomer wrote:
> > >| > kutty wrote:
> > >| > > Hi All,
> > >| > >
> > >| > > I am loading data to a child table from a text file. the text files
> > >| > > also contains data not referenced by parent key. while loading the data
> > >| > > if one row fails to satisfies the constraint everything is getting
> > >| > > rollback..
> > >| > >
> > >| > > plz suggest me something.. which will help me to discard the
> > >| > > unsatisfied rows and continue with the rest..
> > >| > >
> > >| > > My Query:
> > >| > > LOAD DATA CONCURRENT INFILE
> > >| > > '/remote/srm172/saranya/SOURCECOV/common_shell/output/data/c ommon_shell_exec.dat'
> > >| > > INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
> > >| > > (reg_id,func_address);
> > >| > >
> > >| > > Error Msg:
> > >| > > ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> > >| > > constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
> > >| > > `CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
> > >| > > REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))
> > >| > >
> > >| > > Thanks
> > >| > > Devi.
> > >| > >
> > >| > One approach is to load a temporary table which has no
> > >| > constraints and then insert from the temporary to the child only
> > >| > those rows where the foreign key constraint is satisfied.
> > >| >
> > >| > Advantages are that minimal changes are required to your
> > >| > existing load program and the insert can be handled with a
> > >| > simple INSERT INTO child (SELECT * FROM temp WHERE constraints
> > >| > are met); You may have to play around with the syntax -- I can
> > >| > never remember if you need that parenthesis and/or an AS before
> > >| > the SELECT.
> > >| >
> > >| > HTH
> > >| > Jerry
> > ------------------------------------------------------------ ---
> > jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
> > ------------------------------------------------------------ ---