Home » SQL & PL/SQL » SQL & PL/SQL » primary- foreign keys problem...helppppppppppp
primary- foreign keys problem...helppppppppppp [message #184500] Wed, 26 July 2006 16:50 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
I have text files which I process with sqlloader into staging tables. This is done and the staging tables are filled. There are no relations between the staging tables. The data in the staging tables should be processed and insert into the destination tables. However, on the destination side, there is ofcourse relationship between the tables. I need to get the data from the staging tables and insert it into the destination tables.

I know which table I should process first and second and go on.
How can I properly use the foreign keys and primary keys to get the related data from the staging tables and insert these into the destination tables without getting constraint errors because of parent-child relations?

Can anyone please help me out with ideas?
I am thinking about looping through the tables, finding the related tables on the destination side, check the foreign keys and primary keys, but actually I'm not pretty sure about how to accomplish this.

I really do hope that someone can help me out!

Thanks in advance


Jolanda
Re: primary- foreign keys problem...helppppppppppp [message #184502 is a reply to message #184500] Wed, 26 July 2006 16:58 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone please help me out with ideas?
Always insert the parent table before the child table.
Re: primary- foreign keys problem...helppppppppppp [message #184582 is a reply to message #184500] Thu, 27 July 2006 02:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Only post the message in one forum, and pick the best forum for the message?
primary- foreign keys problem...still no solution :-((( [message #184766 is a reply to message #184500] Thu, 27 July 2006 18:35 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Jolanda wrote on Wed, 26 July 2006 16:50

I have text files which I process with sqlloader into staging tables. This is done and the staging tables are filled. There are no relations between the staging tables. The data in the staging tables should be processed and insert into the destination tables. However, on the destination side, there is ofcourse relationship between the tables. I need to get the data from the staging tables and insert it into the destination tables.

I know which table I should process first and second and go on.
How can I properly use the foreign keys and primary keys to get the related data from the staging tables and insert these into the destination tables without getting constraint errors because of parent-child relations?

Can anyone please help me out with ideas?
I am thinking about looping through the tables, finding the related tables on the destination side, check the foreign keys and primary keys, but actually I'm not pretty sure about how to accomplish this.

I really do hope that someone can help me out!

Thanks in advance


Jolanda


Re: primary- foreign keys problem...helppppppppppp [message #184767 is a reply to message #184500] Thu, 27 July 2006 18:55 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>I know which table I should process first and second and go on.
So what is the problem?

With advice, you can have it good, fast, or free; pick any 2.
Re: primary- foreign keys problem...helppppppppppp [message #184816 is a reply to message #184767] Fri, 28 July 2006 03:03 Go to previous messageGo to next message
Jolanda
Messages: 68
Registered: September 2004
Member
hai Anacedent,

Good and free should is ok with me Smile
Re: primary- foreign keys problem...helppppppppppp [message #184831 is a reply to message #184816] Fri, 28 July 2006 04:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I presume the fields that you are going to use for the primary and foreign keys are present in the data in the staging tables (otherwise, how can you know which records wil relate to which)

My aproach would probably be

1.) Create all the Foreign key constraints as DEFERRABLE.
2.) Pick a table with a PK constraint.
3.) Remove any duplicate PKs
4.) Insert this data into the live table
5.) Repeat from 2) until out of PK tables
6.) Insert the data from any other tables into the live tables
7.) For each table (called table A from here on), step through the FK constraints on that table
8.) Using the data in USER_CONSTRAINTS and USER_CONS_COLUMNS, build a query to delete any rows in table A that do not match a row in the corrsponding primary key
9.) Loop back to 8 until you've done all the FK constraints for that table, and then back to 7) until you're out of tables.

This should give you a head start on how to handle the constraint checking.
SQL> 
SQL> create table temp_pk (col_1  varchar2(10), col_2  varchar2(10), col_3 date);

Table created.

SQL> 
SQL> create table temp_fk (r_col_1  varchar2(10), r_col_2  varchar2(10), col_3 number);

Table created.

SQL> 
SQL> alter table temp_pk add constraint temp_pk_pk primary key (col_1,col_2);

Table altered.

SQL> 
SQL> alter table temp_fk add constraint temp_fk_fk foreign key (r_col_1,r_col_2) references temp_pk(col_1,col_2) deferrable initially deferred;

Table altered.

SQL> 
SQL> SELECT fk.constraint_name
  2        ,fk.constraint_type
  3        ,fk.table_name
  4        ,fk.r_constraint_name
  5        ,pk.constraint_type
  6        ,pk.table_name
  7  FROM   user_constraints fk
  8        ,user_constraints pk
  9  WHERE  fk.constraint_name = 'TEMP_FK_FK'
 10  AND    fk.r_constraint_name = pk.constraint_name;

CONSTRAINT_NAME                C TABLE_NAME                     R_CONSTRAINT_NAME              C TABLE_NAME
------------------------------ - ------------------------------ ------------------------------ - ------------------------------
TEMP_FK_FK                     R TEMP_FK                        TEMP_PK_PK                     P TEMP_PK

SQL> 
SQL> SELECT substr(fk.column_name,1,30) fk_column_name
  2        ,substr(pk.column_name,1,30) pk_column_name
  3  from   user_cons_columns fk
  4        ,user_cons_columns pk
  5        ,user_constraints  con
  6  where  con.constraint_name = 'TEMP_FK_FK'
  7  and    fk.constraint_name = con.constraint_name
  8  and    pk.constraint_name = con.r_constraint_name
  9  and    fk.position = pk.position;

FK_COLUMN_NAME                 PK_COLUMN_NAME
------------------------------ ------------------------------
R_COL_1                        COL_1
R_COL_2                        COL_2


Edited to stop point 8 showing up as a Cool

[Updated on: Fri, 28 July 2006 04:28]

Report message to a moderator

Previous Topic: DBMS_METADAYA.GET_XML SYNTAX
Next Topic: stored procedures
Goto Forum:
  


Current Time: Wed Dec 07 14:33:47 CST 2016

Total time taken to generate the page: 0.10028 seconds