Re: cannot validate FK - parents not found
Date: Tue, 23 Sep 2008 05:40:46 GMT
Message-ID: <yj%Bk.3$Z46.2@read4.inet.fi>
Hi DAvid,
thanks for a smart way to do it !
BR,
Timo
"fitzjarrell_at_cox.net" <oratune_at_msn.com> wrote in message
news:648ef910-f132-4949-9a67-eaab45d905dc_at_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 Tue Sep 23 2008 - 00:40:46 CDT