Re: Adding Ref. Integrity

Simon Thompson (scthomp_at_ibm.net) 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.

I found a rather bizzare bug last week where adding ref int constraints will fail if the tables involved has analyzed statistics stored. I had to delete the statistics, add the ref constraints, and re-analyze the tables. the bug is 268878 and is supposedly fixed in 7.1.5 (my current env: 7.1.3, HP-UX)

Johnny Chan
Independent Oracle Specialist Received on Fri Jul 21 1995 - 00:00:00 CEST