RE: Data load ideas

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 4 May 2004 12:15:38 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F877F7@irvmbxw02>

Mr. Joe Testa: The error happens when doing the exchange on the = referenced table, not the table with the foreign key constraint.

SQL> create table father (id number primary key using index local) 2 partition by range (id) (partition fatherp1 values less than = (maxvalue)) ;
Table cr=E9=E9e.
SQL> create table son (id number,
2 foreign key (id) references father (id)) ; Table cr=E9=E9e.

SQL> insert into father (id) values (1) ; 1 ligne cr=E9=E9e.
SQL> insert into son (id) values (1) ;
1 ligne cr=E9=E9e.
SQL> commit ;
Validation effectu=E9e.

SQL> -- create exchange table for parent SQL> create table father_ex (id number primary key) ; Table cr=E9=E9e.
SQL> insert into father_ex (id) values (1) ; 1 ligne cr=E9=E9e.
SQL> insert into father_ex (id) values (2) ; 1 ligne cr=E9=E9e.
SQL> commit ;
Validation effectu=E9e.

SQL> -- exchange parent fails
SQL> alter table father exchange partition fatherp1 with table father_ex = including indexes ;
alter table father exchange partition fatherp1 with table father_ex = including indexes

*

ERREUR =E0 la ligne 1 :
ORA-02266: Les cl=E9s primaires/uniques de la table r=E9f=E9renc=E9es = par des cl=E9s =E9trang=E8res

SQL> -- exchange parent fails even after I add matching fk constraint
SQL> --  from child to exchange table
SQL> alter table son add (foreign key (id) references father_ex (id)) ;

Table modifi=E9e.
SQL> alter table father exchange partition fatherp1 with table father_ex = including indexes ;
alter table father exchange partition fatherp1 with table father_ex = including indexes

*

ERREUR =E0 la ligne 1 :
ORA-02266: Les cl=E9s primaires/uniques de la table r=E9f=E9renc=E9es = par des cl=E9s =E9trang=E8res=20

-----Original Message-----
From: jtesta_at_dmc-it.com

I did some testing since i'm going to implement something like that for the warehouse project i'm working on.

what i'm finding is when i create the table to hold the data(that will = be
exchanged from), if i add the FK to that table before the exchange is done(and everyone is enable validate right now), i have no problem doing the exchange.

so i must be missing something here in the thread, what is it?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 04 2004 - 14:13:18 CDT