Avatar of futureDBA
futureDBA
 asked on

Cant create foreign key?

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

Oracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

you can find them with:

select THD_CUSTNO from MDFTICK
minus
select "Custnum" from MDFRPT
/
johnsone

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61