Re: Adding Ref. Integrity

From: Simon Thompson <scthomp_at_ibm.net>
Date: 1995/07/20
Message-ID: <3ukrj2$3gkg_at_news-s01.ny.us.ibm.net>#1/1


In message <3ujrqr$ai3_at_crcnis3.unl.edu> - nolan_at_helios.unl.edu (Michael Nolan) writes:
:>
:>scthomp_at_ibm.net (Simon Thompson) writes:
:>
:>>alter table CHILD
:>> add (constraint CP_NUMBER
:>> foreign key (C_NUMBER)
:>> references Parent (P_NUMBER);
:>
:>Have you tried checking the table using:
:> select * from child a where not exists (select 'x' from parent b
:> where b.p_number = a.c_number)

Yes.

:>This should display the problem rows in your child table. (problem child?)

It identified some, which I removed. Now it identifies no more, but I still can't add the constraint.

:>I had a problem like this once that turned out to be caused by trailing blanks
:>in a varchar2 column. The data LOOKED fine, but Oracle didn't like it.

All the fields are fully populated. I.e., they are all length 7, and must have 7 digits. I will check though.

I have a job running to use insert from one table to another with the integrity constraint enabled. It will flag any error record. The problem is it will take about 9 hours. Should be finished by the time I get in tomoorow.

Thanks.

+---------------------------
| Simon Thompson

| Christchurch
| New Zealand Received on Thu Jul 20 1995 - 00:00:00 CEST

Original text of this message