Re: Adding Ref. Integrity

scthomp_at_ibm.net (Simon Thompson) wrote:

>I am tring to add referential integrity to an already populated table. I keep
>getting an ORA2294 error - parent keys do not exist.
 

>I have removed all the offending records I can find, and I still get the same
>problem. I have checked, and rechecked, that the parent records exist, and
>can find parent records for all records in the child table. I have checked
>that the fields have primary keys. Any ideas?
 

>The parent table has a field, say: P_NUMBER VARCHAR2(7). This is a primary
>key. (There are other firlds.
 

>The child table has several fields also. The key ones are:
 

> C_NUMBER VARCHAR2(7)
> C_MONTH VARCHAR2(4)
 
>Its primary key is a composite key, C_NUMBER and C_MONTH.
 

>I have checked that all values in C_NUMBER exist in P_NUMBER, and they do.
 

>The code for the constraint is (from memory, its at work):
 

>alter table CHILD
> add (constraint CP_NUMBER
> foreign key (C_NUMBER)
> references Parent (P_NUMBER);
 

>Any help appreciated.

What you have looks ok. Have you tried adding the 'EXCEPTIONS INTO exceptions_table' clause to your constraint? This will at least tell you which rows it is choking on. Also, I presume you are being sure that the constraint name is unique? (This would give you another error, though)

Good luck

--
Bob Blizard    rblizard_at_cmcsys.com     | All these worthwhile
CMC Systems, Inc.                      | opinions are mine, and
175 Littleton Rd   Westford, MA 01886  | not necessarily CMC's...
Ph  (508) 392-1300 Fax (508) 392-1303  |
"If you want to park in the first row of life, 
            you must go there to look for a space."