Re: Primary/foreign key problems

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 14 Oct 1998 14:45:55 GMT
Message-ID: <3625b914.181547561_at_192.86.155.100>


A copy of this was sent to rkalch_at_my-dejanews.com (if that email address didn't require changing) On Wed, 14 Oct 1998 14:28:27 GMT, you wrote:

>This is the error:
>
>ERROR at line 1:
>ORA-02294: cannot add referential constraint - parent keys not found
>
>and this is what I'm trying to do:
>
>ALTER TABLE ORDERS1
>ADD FOREIGN KEY (MFR, PRODUCT)
>REFERENCES PRODUCTS1 (MFR_ID, PRODUCT_ID);
>
>I already added a primary key for PRODUCTS1 (MFR_ID, PRODUCT_ID) and the table
>was altered successfully.
>
>I would welcome any help.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

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 - 16:45:55 CEST

Original text of this message