Re: cannot validate FK - parents not found
Date: Fri, 19 Sep 2008 05:43:50 -0700 (PDT)
Message-ID: <5dbff7b3-b222-4658-842a-6f63879d5c71@l64g2000hse.googlegroups.com>
On Sep 19, 2:56 am, "timo" <timo.ta..._at_luukku.com> wrote:
> Hi,
> what's the most efficient way to search rows in a (big) table preventing FK
> to be created;
> we've a table where PK consists of several columns and those columns are
> referenced by child tables.
>
> And as the table has already rows with problems those should be identified
> easily and corrected - so now we get
> ORA-02298: cannot validate (AAA.BBBBB_FK) - parent keys not found
>
> BR, Timo
Let Oracle find them for you by creating the EXCEPTIONS table and using it as shown below:
SQL> @?/rdbms/admin/utlexcpt SQL> rem SQL> rem $Header: utlexcpt.sql,v 1.1 1992/10/20 11:57:02 GLUMPKIN Stab$
SQL> rem SQL> Rem Copyright (c) 1991 by Oracle Corporation SQL> Rem NAME SQL> Rem except.sql - <one-line expansion of the name> SQL> Rem DESCRIPTION SQL> Rem <short description of component this file declares/defines>
SQL> Rem RETURNS SQL> Rem SQL> Rem NOTES SQL> Rem <other useful comments, qualifications, etc.> SQL> Rem MODIFIED (MM/DD/YY) SQL> Rem glumpkin 10/20/92 - Renamed from EXCEPT.SQL SQL> Rem epeeler 07/22/91 - add comma SQL> Rem epeeler 04/30/91 - Creation SQL> SQL> create table exceptions(row_id rowid, 2 owner varchar2(30), 3 table_name varchar2(30), 4 constraint varchar2(30));
Table created.
SQL>
SQL> create table parents(
2 my_id number, 3 mydata varchar2(40)
4 );
Table created.
SQL>
SQL> alter table parents
2 add constraint parents_pk
3 primary key(my_id);
Table altered.
SQL>
SQL> create table children(
2 child_id number, 3 child_loc varchar2(40), 4 parent_id number
5 );
Table created.
SQL>
SQL> alter table children
2 add constraint children_pk
3 primary key(child_id);
Table altered.
SQL>
SQL> begin
2 for i in 1..100000 loop 3 insert into parents 4 values(i, 'Location #'||i); 5 end loop; 6 7 for i in 1..100100 loop 8 insert into children 9 values(i-1, 'Sub-location #'||i, i); 10 end loop; 11 12 commit;
13
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> alter table children
2 add constraint parent_child_fk
3 foreign key (parent_id) references parents(my_id)
4 exceptions into exceptions;
add constraint parent_child_fk
*
ERROR at line 2:
ORA-02298: cannot validate (BING.PARENT_CHILD_FK) - parent keys not
found
SQL>
SQL> select count(*) from exceptions;
COUNT(*)
100
SQL>
SQL> select owner, table_name, rowid
2 from exceptions;
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAA BING CHILDREN AAAYpMAAEAAAAwsAAB BING CHILDREN AAAYpMAAEAAAAwsAAC BING CHILDREN AAAYpMAAEAAAAwsAAD BING CHILDREN AAAYpMAAEAAAAwsAAE BING CHILDREN AAAYpMAAEAAAAwsAAF BING CHILDREN AAAYpMAAEAAAAwsAAG BING CHILDREN AAAYpMAAEAAAAwsAAH BING CHILDREN AAAYpMAAEAAAAwsAAI BING CHILDREN AAAYpMAAEAAAAwsAAJ BING CHILDREN
AAAYpMAAEAAAAwsAAK
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAL BING CHILDREN AAAYpMAAEAAAAwsAAM BING CHILDREN AAAYpMAAEAAAAwsAAN BING CHILDREN AAAYpMAAEAAAAwsAAO BING CHILDREN AAAYpMAAEAAAAwsAAP BING CHILDREN AAAYpMAAEAAAAwsAAQ BING CHILDREN AAAYpMAAEAAAAwsAAR BING CHILDREN AAAYpMAAEAAAAwsAAS BING CHILDREN AAAYpMAAEAAAAwsAAT BING CHILDREN AAAYpMAAEAAAAwsAAU BING CHILDREN
AAAYpMAAEAAAAwsAAV
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAW BING CHILDREN AAAYpMAAEAAAAwsAAX BING CHILDREN AAAYpMAAEAAAAwsAAY BING CHILDREN AAAYpMAAEAAAAwsAAZ BING CHILDREN AAAYpMAAEAAAAwsAAa BING CHILDREN AAAYpMAAEAAAAwsAAb BING CHILDREN AAAYpMAAEAAAAwsAAc BING CHILDREN AAAYpMAAEAAAAwsAAd BING CHILDREN AAAYpMAAEAAAAwsAAe BING CHILDREN AAAYpMAAEAAAAwsAAf BING CHILDREN
AAAYpMAAEAAAAwsAAg
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAh BING CHILDREN AAAYpMAAEAAAAwsAAi BING CHILDREN AAAYpMAAEAAAAwsAAj BING CHILDREN AAAYpMAAEAAAAwsAAk BING CHILDREN AAAYpMAAEAAAAwsAAl BING CHILDREN AAAYpMAAEAAAAwsAAm BING CHILDREN AAAYpMAAEAAAAwsAAn BING CHILDREN AAAYpMAAEAAAAwsAAo BING CHILDREN AAAYpMAAEAAAAwsAAp BING CHILDREN AAAYpMAAEAAAAwsAAq BING CHILDREN
AAAYpMAAEAAAAwsAAr
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAAs BING CHILDREN AAAYpMAAEAAAAwsAAt BING CHILDREN AAAYpMAAEAAAAwsAAu BING CHILDREN AAAYpMAAEAAAAwsAAv BING CHILDREN AAAYpMAAEAAAAwsAAw BING CHILDREN AAAYpMAAEAAAAwsAAx BING CHILDREN AAAYpMAAEAAAAwsAAy BING CHILDREN AAAYpMAAEAAAAwsAAz BING CHILDREN AAAYpMAAEAAAAwsAA0 BING CHILDREN AAAYpMAAEAAAAwsAA1 BING CHILDREN
AAAYpMAAEAAAAwsAA2
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsAA3 BING CHILDREN AAAYpMAAEAAAAwsAA4 BING CHILDREN AAAYpMAAEAAAAwsAA5 BING CHILDREN AAAYpMAAEAAAAwsAA6 BING CHILDREN AAAYpMAAEAAAAwsAA7 BING CHILDREN AAAYpMAAEAAAAwsAA8 BING CHILDREN AAAYpMAAEAAAAwsAA9 BING CHILDREN AAAYpMAAEAAAAwsAA+ BING CHILDREN AAAYpMAAEAAAAwsAA/ BING CHILDREN AAAYpMAAEAAAAwsABA BING CHILDREN
AAAYpMAAEAAAAwsABB
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABC BING CHILDREN AAAYpMAAEAAAAwsABD BING CHILDREN AAAYpMAAEAAAAwsABE BING CHILDREN AAAYpMAAEAAAAwsABF BING CHILDREN AAAYpMAAEAAAAwsABG BING CHILDREN AAAYpMAAEAAAAwsABH BING CHILDREN AAAYpMAAEAAAAwsABI BING CHILDREN AAAYpMAAEAAAAwsABJ BING CHILDREN AAAYpMAAEAAAAwsABK BING CHILDREN AAAYpMAAEAAAAwsABL BING CHILDREN
AAAYpMAAEAAAAwsABM
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABN BING CHILDREN AAAYpMAAEAAAAwsABO BING CHILDREN AAAYpMAAEAAAAwsABP BING CHILDREN AAAYpMAAEAAAAwsABQ BING CHILDREN AAAYpMAAEAAAAwsABR BING CHILDREN AAAYpMAAEAAAAwsABS BING CHILDREN AAAYpMAAEAAAAwsABT BING CHILDREN AAAYpMAAEAAAAwsABU BING CHILDREN AAAYpMAAEAAAAwsABV BING CHILDREN AAAYpMAAEAAAAwsABW BING CHILDREN
AAAYpMAAEAAAAwsABX
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN AAAYpMAAEAAAAwsABY BING CHILDREN AAAYpMAAEAAAAwsABZ BING CHILDREN AAAYpMAAEAAAAwsABa BING CHILDREN AAAYpMAAEAAAAwsABb BING CHILDREN AAAYpMAAEAAAAwsABc BING CHILDREN AAAYpMAAEAAAAwsABd BING CHILDREN AAAYpMAAEAAAAwsABe BING CHILDREN AAAYpMAAEAAAAwsABf BING CHILDREN AAAYpMAAEAAAAwsABg BING CHILDREN AAAYpMAAEAAAAwsABh BING CHILDREN
AAAYpMAAEAAAAwsABi
OWNER TABLE_NAMEROWID
------------------------------ ------------------------------ ------------------ BING CHILDREN
AAAYpMAAEAAAAwsABj
100 rows selected.
SQL> David Fitzjarrell Received on Fri Sep 19 2008 - 07:43:50 CDT