Re: please help : newbie question - 'parent keys not found'

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 14 Oct 1998 13:38:30 GMT
Message-ID: <3631a8df.177398155@192.86.155.100>

A copy of this was sent to "Marco Nedermeijer" <m.n.nedermeijer_at_rtd.nl> (if that email address didn't require changing) On Wed, 14 Oct 1998 14:42:20 +0200, you wrote:

>Hello,
>
>When i create a foreign key on a table i get the message 'parent keys not
>found'.
>
>The primary key of the parent table a two columns combined.
>The foreign key of the child table are also two combined columns.
>
>Please help !
>
>Marco Nedermeijer
>m.n.nedermeijer_at_rtd.nl
>

That means you have keys in the child table that do not exist in the parent. You can either run a query to find them (eg: select * from C where NOT EXISTS ( select null from P where p.pk1 = c.fk1 and p.pk2 = c.fk2 ) ) or use the EXCEPTIONS clause on the constraint statement. For example:

SQL> create table exceptions(row_id rowid,

  2                              owner varchar2(30),
  3                              table_name varchar2(30),
  4                                  constraint varchar2(30));

Table created.

SQL> create table p ( x int, y int, primary key(x,y) ); Table created.

SQL> create table c ( x int, y int );
Table created.

SQL> insert into p values ( 1, 1 );
SQL> insert into p values ( 2, 2 );
SQL> insert into p values ( 4, 4 );

SQL> insert into c values ( 1, 1 );
SQL> insert into c values ( 2, 2 );

SQL> insert into c values ( 3, 3 );
SQL> insert into c values ( 4, 4 );

SQL> alter table c add constraint c_fk foreign key (x,y) references p(x,y)   2 EXCEPTIONS INTO exceptions;
alter table c add constraint c_fk foreign key (x,y) references p(x,y) *
ERROR at line 1:
ORA-02298: cannot enable (TKYTE.C_FK) - parent keys not found

SQL> select *
  2 from c
  3 where rowid in ( select row_id

  4                                          from exceptions )

  5 /

         X Y
---------- ----------

         3 3

so that shows us all of the rows in C that violate the constraint....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Oct 14 1998 - 08:38:30 CDT