Re: cannot validate FK - parents not found

From: <fitzjarrell_at_cox.net>
Date: Fri, 19 Sep 2008 10:52:46 -0700 (PDT)
Message-ID: <648ef910-f132-4949-9a67-eaab45d905dc@d45g2000hsc.googlegroups.com>


On Sep 19, 7:43 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> 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_NAME
> ROWID
> ------------------------------ ------------------------------
> ------------------
> BING                           CHILDREN
> AAAYpMAAEAAAAwsABj
>
> 100 rows selected.
>
> SQL>
>
> David Fitzjarrell

An error on my part left the output from my prior post unusable. The correct query, and output, are:

SQL> select owner, table_name, row_id
  2 from exceptions;

OWNER TABLE_NAME ROW_ID

---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xACK
BING       CHILDREN   AAAYp0AAEAAAF4xACL
BING       CHILDREN   AAAYp0AAEAAAF4xACM
BING       CHILDREN   AAAYp0AAEAAAF4xACN
BING       CHILDREN   AAAYp0AAEAAAF4xACO
BING       CHILDREN   AAAYp0AAEAAAF4xACP
BING       CHILDREN   AAAYp0AAEAAAF4xACQ
BING       CHILDREN   AAAYp0AAEAAAF4xACR
BING       CHILDREN   AAAYp0AAEAAAF4xACS
BING       CHILDREN   AAAYp0AAEAAAF4xACT
BING       CHILDREN   AAAYp0AAEAAAF4xACU

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xACV
BING       CHILDREN   AAAYp0AAEAAAF4xACW
BING       CHILDREN   AAAYp0AAEAAAF4xACX
BING       CHILDREN   AAAYp0AAEAAAF4xACY
BING       CHILDREN   AAAYp0AAEAAAF4xACZ
BING       CHILDREN   AAAYp0AAEAAAF4xACa
BING       CHILDREN   AAAYp0AAEAAAF4xACb
BING       CHILDREN   AAAYp0AAEAAAF4xACc
BING       CHILDREN   AAAYp0AAEAAAF4xACd
BING       CHILDREN   AAAYp0AAEAAAF4xACe
BING       CHILDREN   AAAYp0AAEAAAF4xACf

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xACg
BING       CHILDREN   AAAYp0AAEAAAF4xACh
BING       CHILDREN   AAAYp0AAEAAAF4xACi
BING       CHILDREN   AAAYp0AAEAAAF4xACj
BING       CHILDREN   AAAYp0AAEAAAF4xACk
BING       CHILDREN   AAAYp0AAEAAAF4xACl
BING       CHILDREN   AAAYp0AAEAAAF4xACm
BING       CHILDREN   AAAYp0AAEAAAF4xACn
BING       CHILDREN   AAAYp0AAEAAAF4xACo
BING       CHILDREN   AAAYp0AAEAAAF4xACp
BING       CHILDREN   AAAYp0AAEAAAF4xACq

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xACr
BING       CHILDREN   AAAYp0AAEAAAF4xACs
BING       CHILDREN   AAAYp0AAEAAAF4xACt
BING       CHILDREN   AAAYp0AAEAAAF4xACu
BING       CHILDREN   AAAYp0AAEAAAF4xACv
BING       CHILDREN   AAAYp0AAEAAAF4xACw
BING       CHILDREN   AAAYp0AAEAAAF4xACx
BING       CHILDREN   AAAYp0AAEAAAF4xACy
BING       CHILDREN   AAAYp0AAEAAAF4xACz
BING       CHILDREN   AAAYp0AAEAAAF4xAC0
BING       CHILDREN   AAAYp0AAEAAAF4xAC1

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xAC2
BING       CHILDREN   AAAYp0AAEAAAF4xAC3
BING       CHILDREN   AAAYp0AAEAAAF4xAC4
BING       CHILDREN   AAAYp0AAEAAAF4xAC5
BING       CHILDREN   AAAYp0AAEAAAF4xAC6
BING       CHILDREN   AAAYp0AAEAAAF4xAC7
BING       CHILDREN   AAAYp0AAEAAAF4xAC8
BING       CHILDREN   AAAYp0AAEAAAF4xAC9
BING       CHILDREN   AAAYp0AAEAAAF4xAC+
BING       CHILDREN   AAAYp0AAEAAAF4xAC/
BING       CHILDREN   AAAYp0AAEAAAF4xADA

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xADB
BING       CHILDREN   AAAYp0AAEAAAF4xADC
BING       CHILDREN   AAAYp0AAEAAAF4xADD
BING       CHILDREN   AAAYp0AAEAAAF4xADE
BING       CHILDREN   AAAYp0AAEAAAF4xADF
BING       CHILDREN   AAAYp0AAEAAAF4xADG
BING       CHILDREN   AAAYp0AAEAAAF4xADH
BING       CHILDREN   AAAYp0AAEAAAF4xADI
BING       CHILDREN   AAAYp0AAEAAAF4xADJ
BING       CHILDREN   AAAYp0AAEAAAF4xADK
BING       CHILDREN   AAAYp0AAEAAAF4xADL

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF4xADM
BING       CHILDREN   AAAYp0AAEAAAF41AAA
BING       CHILDREN   AAAYp0AAEAAAF41AAB
BING       CHILDREN   AAAYp0AAEAAAF41AAC
BING       CHILDREN   AAAYp0AAEAAAF41AAD
BING       CHILDREN   AAAYp0AAEAAAF41AAE
BING       CHILDREN   AAAYp0AAEAAAF41AAF
BING       CHILDREN   AAAYp0AAEAAAF41AAG
BING       CHILDREN   AAAYp0AAEAAAF41AAH
BING       CHILDREN   AAAYp0AAEAAAF41AAI
BING       CHILDREN   AAAYp0AAEAAAF41AAJ

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF41AAK
BING       CHILDREN   AAAYp0AAEAAAF41AAL
BING       CHILDREN   AAAYp0AAEAAAF41AAM
BING       CHILDREN   AAAYp0AAEAAAF41AAN
BING       CHILDREN   AAAYp0AAEAAAF41AAO
BING       CHILDREN   AAAYp0AAEAAAF41AAP
BING       CHILDREN   AAAYp0AAEAAAF41AAQ
BING       CHILDREN   AAAYp0AAEAAAF41AAR
BING       CHILDREN   AAAYp0AAEAAAF41AAS
BING       CHILDREN   AAAYp0AAEAAAF41AAT
BING       CHILDREN   AAAYp0AAEAAAF41AAU

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF41AAV
BING       CHILDREN   AAAYp0AAEAAAF41AAW
BING       CHILDREN   AAAYp0AAEAAAF41AAX
BING       CHILDREN   AAAYp0AAEAAAF41AAY
BING       CHILDREN   AAAYp0AAEAAAF41AAZ
BING       CHILDREN   AAAYp0AAEAAAF41AAa
BING       CHILDREN   AAAYp0AAEAAAF41AAb
BING       CHILDREN   AAAYp0AAEAAAF41AAc
BING       CHILDREN   AAAYp0AAEAAAF41AAd
BING       CHILDREN   AAAYp0AAEAAAF41AAe
BING       CHILDREN   AAAYp0AAEAAAF41AAf

OWNER      TABLE_NAME ROW_ID
---------- ---------- ------------------
BING       CHILDREN   AAAYp0AAEAAAF41AAg

100 rows selected.

SQL> To return the missing parent key values:

SQL> select parent_id
  2 from children
  3 where rowid in (select row_id from exceptions where table_name = 'CHILDREN');  PARENT_ID


    100001
    100002
    100003
    100004
    100005
    100006
    100007
    100008
    100009
    100010
    100011

 PARENT_ID


    100012
    100013
    100014
    100015
    100016
    100017
    100018
    100019
    100020
    100021
    100022

 PARENT_ID


    100023
    100024
    100025
    100026
    100027
    100028
    100029
    100030
    100031
    100032
    100033

 PARENT_ID


    100034
    100035
    100036
    100037
    100038
    100039
    100040
    100041
    100042
    100043
    100044

 PARENT_ID


    100045
    100046
    100047
    100048
    100049
    100050
    100051
    100052
    100053
    100054
    100055

 PARENT_ID


    100056
    100057
    100058
    100059
    100060
    100061
    100062
    100063
    100064
    100065
    100066

 PARENT_ID


    100067
    100068
    100069
    100070
    100071
    100072
    100073
    100074
    100075
    100076
    100077

 PARENT_ID


    100078
    100079
    100080
    100081
    100082
    100083
    100084
    100085
    100086
    100087
    100088

 PARENT_ID


    100089
    100090
    100091
    100092
    100093
    100094
    100095
    100096
    100097
    100098
    100099

 PARENT_ID


    100100

100 rows selected.

SQL> David Fitzjarrell Received on Fri Sep 19 2008 - 12:52:46 CDT

Original text of this message