Foreign Key

From: Rainer Scheel <rainer.scheel_at_sno.drs1.x400.sni.de>
Date: Wed, 11 Nov 1998 11:53:34 +0100
Message-ID: <36496CAD.F8820A07@sno.drs1.x400.sni.de>

In an Oracle 7.3.3 database I have (among others) a table LIC_USTS with a PK:

SQL> select constraint_type, table_name, status from user_constraints where constraint_name = 'LIC_UST_PK';

C TABLE_NAME                     STATUS
- ------------------------------ --------
P LIC_USTS                       ENABLED

SQL> select table_name, column_name from user_cons_columns where constraint_name='LIC_UST_PK';

TABLE_NAME                     COLUMN_NAME


------------------------------ ------------------------------


LIC_USTS                       ID_LIC

SQL> Another table MARR_CERTS defines a foreign key on that PK:

SQL> select constraint_type, r_constraint_name, table_name, delete_rule, status
from user_constraints where constraint_name = 'MARR_CERT_LIC_UST_FK';

C R_CONSTRAINT_NAME TABLE_NAME DELETE_RU STATUS

------------------------------ ------------------------------ --------- -------- R LIC_UST_PK MARR_CERTS CASCADE ENABLED

SQL> select table_name, column_name from user_cons_columns where constraint_name

'MARR_CERT_LIC_UST_FK';

TABLE_NAME                     COLUMN_NAME


------------------------------ ------------------------------


MARR_CERTS                     ID_LIC_PARTNER_1

SQL> But with

SQL> select id_lic_partner_1 from marr_certs minus select id_lic from lic_usts;

ID_LIC_PARTNER_1

122737

SQL> I get a record in MARR_CERTS which violates the foreign key. Now I am worried about the integrity of my db. I exported my schema into a .dmp-file and tried to import it into another db.
During the import I got
ORA-02298: cannot enable (RAZ.MARR_CERT_LIC_UST_FK) - parent keys not found
which is correct I believe.
Could someone enlighten me what's going on in my db? How could this inconsistency occur?
How can I save my data in future?
PS: Both ID_LIC and ID_LIC_PARTNER_1 are NUMBER(9) NOT NULL

Rainer Scheel
Software Engineer
Siemens Business Services GmbH & Co OHG Received on Wed Nov 11 1998 - 04:53:34 CST