Cant create foreign key?

futureDBA
futureDBA used Ask the Experts™
on
i am trying to create a foreign key i am being given error

SQL Error: ORA-02298: cannot validate (ARIEL.MDFTICK_FK1) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious

i am using "alter table MDFTICK add constraint MDTICK_FK foreign key (THD_CUSTNO) REFERENCES MDFRPT("Custnum");//"


table 1
CREATE TABLE "ARIEL"."MDFRPT"
  (
    "Custnum"          NUMBER(6,0) NOT NULL ENABLE,
    "CM_CUSTMR_NAME"   CHAR(90 BYTE) NOT NULL ENABLE,
    "CM_CUSTMR_ADDR_1" CHAR(90 BYTE) NOT NULL ENABLE,
    "PHONE"            CHAR(39 BYTE),
    "CM_CONTACT"       CHAR(60 BYTE) NOT NULL ENABLE,
    "CM_CHNID"         CHAR(9 BYTE) NOT NULL ENABLE,
    "30"               NUMBER,
    "Current Period"   NUMBER,
    "CP With Ticket"   NUMBER,
    "Second Period"    NUMBER,
    "Third Period"     NUMBER,
    "Fourth Period"    NUMBER,
    "Total Balance"    NUMBER(11,2) NOT NULL ENABLE,
    CONSTRAINT "MDFRPT_PK" PRIMARY KEY ("Custnum") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SALES_ARIELS" ENABLE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "SALES_ARIELS" ;

Open in new window


table2
CREATE TABLE "ARIEL"."MDFTICK"
  (
    "THD_CUSTNO" NUMBER(6,0) NOT NULL ENABLE,
    "THD_TICKNO" NUMBER(10,0) NOT NULL ENABLE,
    "TH_DATE" DATE,
    "TH_PRODNO"      NUMBER(8,0) NOT NULL ENABLE,
    "TH_CRDFLG"      NUMBER(2,0) NOT NULL ENABLE,
    "TH_PRICE"       NUMBER(9,5) NOT NULL ENABLE,
    "TH_COST"        NUMBER(9,5) NOT NULL ENABLE,
    "TH_DOLRS"       NUMBER(11,2) NOT NULL ENABLE,
    "TH_UNITS"       NUMBER(9,2) NOT NULL ENABLE,
    "TH_KEYED_PRICE" NUMBER(1,0) NOT NULL ENABLE,
    CONSTRAINT "MDFTICK_PK" PRIMARY KEY ("THD_CUSTNO", "THD_TICKNO", "TH_DATE", "TH_PRODNO", "TH_CRDFLG") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SALES_ARIELS" ENABLE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "SALES_ARIELS" ;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>"cannot validate (%s.%s) - parent keys not found"


you have rows in MDFTICK with a value in THD_CUSTNO that does not have a value in MDFRPT "Custnum".
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
you can find them with:

select THD_CUSTNO from MDFTICK
minus
select "Custnum" from MDFRPT
/
johnsoneSenior Oracle DBA

Commented:
Another way to find them is with the EXCEPTIONS clause.

You need to create a table first, but there is a script supplied.

The table is created with $ORACLE_HOME/rdbms/admin/utlexcpt.sql

You can run that within SQL*Plus by connecting as the user you are using to create the constraint and running:
@?/rdbms/admin/utlexcpt

Open in new window

Then run your constraint adding the exceptions clause:
ALTER TABLE MDFTICK 
  ADD CONSTRAINT mdtick_fk FOREIGN KEY (THD_CUSTNO) REFERENCES MDFRPT("Custnum") 
  EXCEPTIONS INTO exceptions; 

Open in new window

Then you should be able to query the EXCEPTIONS table.  It will have the ROWIDs of all the rows that violate the constraint.  You can use this to narrow down which records are missing in the parent table pretty easily.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial