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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.