Re: Foreign Key Integrity Constraints

On 10 Sep 1996 18:12:01 GMT, Carol KIlner <kilnerc_at_basf-corp.com> wrote:

>Could someone tell me if what I am trying to do is possible.
>
>(1) I have setup Foreign key constraints on a table and used
>the exceptions into clause as below.
>
>alter table order_header
> add constraint fk_customer_cd
> foreign key (customer_cd,record_source_ind)
> references customer_master (customer_cd,record_source_ind)
> exceptions into exceptions_order_header;
>
>(2) I disable all constraints and load the table using SQL*LDR
>
>(3) I enable the constraints and I get the following error message:
>alter table order_header
>*
>ERROR at line 1:
>ORA-02298: cannot enable constraint (SALESSYS.FK_CUSTOMER_CD) - parent keys not
>found
>
>Should these records that violate the constraint no be moved to the exceptions
>table?
>
>Any help would be much appreciated.
>
>Thanks,
>Carol Kilner
>BASF Canada
>kilnerc_at_basf-corp.com
>
>

As long as the parent table is loaded, and it has a primary key as mentioned in your alter statement above, you shouldn't have a problem. But, there must be a matching parent for each child or you get the error you got. You might want to see why there is no matching parent key (record), apparently you have some order_header records with invalid customer_cd, record_source_ind combinations.

After you run SQL*LOADER and before enabling the constraints run this statement to determine where the failures will occur, then fix the parent table.

select distinct customer_cd, record_source_ind from order_header
MINUS
select customer_cd, record_source_ind
from customer_master;

The result set will contain only those records without a match in the parent table.

Jim Joslin Received on Fri Sep 13 1996 - 00:00:00 CEST