problem on loading data-file to child-table while setting integrity all for table immediate unchecked 
Author Message
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked
I need some help to deal with the following issue.

a child-table has F.K. pointing to parent-table, I want to load
data-file to this child-table first then insert into parent-table. the
commands issued were

set integrity for child-table all immediate unchecked
set constraints for child-table all immediate unchecked

import ...

unfortunately it seemed the above "setting" didn't do what expected
and saying F.K. invalid, error code 23503

Can anyone tell me how to do it?

Thanks

--
John Wu
Toronto



Mon, 18 Jul 2005 12:43:35 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked

One way to do what you want is:
Alter the child table and remove the foreign key constraint,
load the data and then add the FK constraint again.

Quote:

> I need some help to deal with the following issue.

> a child-table has F.K. pointing to parent-table, I want to load
> data-file to this child-table first then insert into parent-table. the
> commands issued were

> set integrity for child-table all immediate unchecked
> set constraints for child-table all immediate unchecked

> import ...

> unfortunately it seemed the above "setting" didn't do what expected
> and saying F.K. invalid, error code 23503

> Can anyone tell me how to do it?

> Thanks

> --
> John Wu
> Toronto

--
Anton Versteeg
DB2 Specialist
IBM Netherlands


Mon, 18 Jul 2005 19:21:09 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked
TRY Using LOAD instead of IMPORT

--- Kishore

Quote:

> One way to do what you want is:
> Alter the child table and remove the foreign key constraint,
> load the data and then add the FK constraint again.


> > I need some help to deal with the following issue.

> > a child-table has F.K. pointing to parent-table, I want to load
> > data-file to this child-table first then insert into parent-table. the
> > commands issued were

> > set integrity for child-table all immediate unchecked
> > set constraints for child-table all immediate unchecked

> > import ...

> > unfortunately it seemed the above "setting" didn't do what expected
> > and saying F.K. invalid, error code 23503

> > Can anyone tell me how to do it?

> > Thanks

> > --
> > John Wu
> > Toronto



Tue, 19 Jul 2005 00:36:21 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked
I think you misread the documentation. You should use
set integrity for child-table OFF

IMMEDIATE UNCHECKED
Specifies one of the following:
The table is to have its integrity checking turned on (and, thus, are
to be taken out of the check pending state) without having the table
checked for integrity violations or the summary table is to have
immediate refreshing turned on and be taken out of check pending state
...

regards,
dmitri



Tue, 19 Jul 2005 07:25:41 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked

Quote:

> I think you misread the documentation. You should use
> set integrity for child-table OFF

> IMMEDIATE UNCHECKED
> Specifies one of the following:
> The table is to have its integrity checking turned on (and, thus, are
> to be taken out of the check pending state) without having the table
> checked for integrity violations or the summary table is to have
> immediate refreshing turned on and be taken out of check pending state
> ...

> regards,
> dmitri

Thanks,
but whenever inserting, it still said
The underlying table(or dependent table) is in the Check pending state(57016)

--
John
Toronto



Tue, 19 Jul 2005 23:40:26 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked
set integrity does not work for you as it leaves the table in check pending
stat (so you can't insert into it).

My question is why are you attempting to insert child records that have no
parents? This is breaking your stated databases constraints (i.e. the FK), so
what else would you expect the database to do?
It's like saying CHECK(COL IN ('Y','N')) then complaining when the database
does not let you insert an 'U'.

If, on the other hand you are trying to insert Parent and Child rows in one
atomic statement (tradionally called a 'transaction'), and getting annoyed
that the order of statements within such an atomic statement (if that is not a
contradiction in terms) is a factor on whether the database will accept the
statement or not, then indeed you do have a complaint.

Regards
Paul Vernon
Business Intelligence, IBM Global Services



Quote:
> > I think you misread the documentation. You should use
> > set integrity for child-table OFF

> > IMMEDIATE UNCHECKED
> > Specifies one of the following:
> > The table is to have its integrity checking turned on (and, thus, are
> > to be taken out of the check pending state) without having the table
> > checked for integrity violations or the summary table is to have
> > immediate refreshing turned on and be taken out of check pending state
> > ...

> > regards,
> > dmitri

> Thanks,
> but whenever inserting, it still said
> The underlying table(or dependent table) is in the Check pending
state(57016)

> --
> John
> Toronto



Wed, 20 Jul 2005 00:53:45 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked
Sorry, but I think I misread you question as well.
You could not insert records into the tables in check pending state
I think your only option is to drop/create fk (or fix the import order)

BTW,
do you really need this FK constraint?

regards,
dmitri



Wed, 20 Jul 2005 05:54:09 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked

Quote:

> set integrity does not work for you as it leaves the table in check pending
> stat (so you can't insert into it).

> My question is why are you attempting to insert child records that have no
> parents? This is breaking your stated databases constraints (i.e. the FK), so
> what else would you expect the database to do?
> It's like saying CHECK(COL IN ('Y','N')) then complaining when the database
> does not let you insert an 'U'.

> If, on the other hand you are trying to insert Parent and Child rows in one
> atomic statement (tradionally called a 'transaction'), and getting annoyed
> that the order of statements within such an atomic statement (if that is not a
> contradiction in terms) is a factor on whether the database will accept the
> statement or not, then indeed you do have a complaint.

> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services

Thanks.
The reason why I wanted F.K. constraint deferred(or insert data into
child-table first then into parent-table) is that the input-data for
parent-table and child-table are laid in a file randomly, and I don't
want to alter table whenever inserting (that may affect other
transections). It's guaranteed that eventually all records in
child-table can find their own key in parent-table.

--
Regards
John
Toronto



Wed, 20 Jul 2005 09:23:02 GMT
 problem on loading data-file to child-table while setting integrity all for table immediate unchecked

[snip]>

Quote:
> Thanks.
> The reason why I wanted F.K. constraint deferred(or insert data into
> child-table first then into parent-table) is that the input-data for
> parent-table and child-table are laid in a file randomly, and I don't
> want to alter table whenever inserting (that may affect other
> transections). It's guaranteed that eventually all records in
> child-table can find their own key in parent-table.

Humm, I'm afraid that there is no neat solution for you here. Not until SQL
gets the ability to do multiple assignment at least. In the meantime your
options are

I *think* this would work
Use a BEFORE trigger on the parent to insert a dummy child record if no
children exist for a given parent, then a trigger on the child to delete any
dummy for the same parent.

or

import your rows into a temp table, then insert your parent rows before the
child rows...

or

/* Start transaction */
drop the FK constraint ;
insert your rows;
recreate the FK constraint;
COMMIT

or (in V8)

/* Start transaction */
alter the FK constraint to be information only;
insert your rows;
alter the FK constraint to be system checked;
COMMIT

Regards
Paul Vernon
Business Intelligence, IBM Global Services



Fri, 22 Jul 2005 21:51:58 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. How to execute a Create table or alter table table everytime before a data load

2. Problem with appending one table to two tables - referential integrity - help please

3. Loading data from ASCII files into tables

4. Loading data into a SQL Server table from a .dbf file

5. Parent - Child Table Loads

6. Loading data from a .dbf file into a SQL Server table

7. Using a BCP file created by MSSQL to load into sybase table data

8. Summary tables -SET CONSTRAINTS ......IMMEDIATE CHECKED;

9. How to set referential integrity on linked tables...

10. Data environment Table loading problem

11. Data integrity for relational databases (parent/child relationships)

12. table data type for immediate use only?


 
Powered by phpBB® Forum Software