Relational Data Issues, Fixing

Missing Parent Records

1. Test if any child records exist without parent records.

SELECT count(*) 
FROM <Child_Table>
WHERE fkID NOT IN (SELECT <pkID> FROM <Parent_Table>);

SELECT count(*) 
FROM SCOTT.DocumentPages 
WHERE DocumentID NOT IN (SELECT DocumentID FROM SCOTT.Documents);

2. If so delete them (assuming you have a current export).

DELETE FROM <Child_Table>
WHERE <fkID> NOT IN (SELECT <pkID> FROM <Parent_Table>);

DELETE FROM SCOTT.DocumentPages 
WHERE DocumentID NOT IN (SELECT DocumentID FROM SCOTT.Documents);

Purging Child Records

What follows is an example scenario of deleting child records inhibiting the deletion of a parent record. This assumes of course that cascading deletes are not used or similar reason for bad child records to exist at all.

Say you try to delete a record in the table MEMBERS with the PK of 42 and you get an integrity constraint error as shown:

DELETE FROM HR.MEMBERS WHERE MemberID=42;

ORA-02292: integrity constraint error (HR.FK_MemAppDat_MemApp) violated - child record exists.

0. Confirm the PK column in the parent table: desc HR.MEMBERS;

   Name                       Null?     Type
   -------------------------- -------   ------------------  
   MemberID                   NOT NULL  Number(38)
   ...

1. From your starting table (MEMBERS) run fk_ForTable.sql to get FKs table info (see below APPENDIX for script).

   Script output below:
      
   Constraint_Name              Table_Name             R_Constraint_Name
   ---------------------------  ---------------------  --------------------------
  >>FK_MemAppDat_MemApp          MemApplicationData     PK_MemApplication
    FK_ProdInrst_MemAppN         ProductInterest        PK_MemApplication

2. Using the script output find the constraint listed in the error (FK_MemAppDat_MemApp) then identify the corresponding the child table.

   In this case it would be: MemApplicationData

3. From the corresponding the child table identify its PK column: desc HR.MemApplicationData;

   
   Name                       Null?     Type
   -------------------------- -------   ------------------  
   MemberApplicationDataID    NOT NULL  Number(38)
   ...

4. Find the FKs (IDs) matching the parent PK (ID). In this example: 42

   This will be needed if there are multiple levels of child data to purge.
   
   SELECT MemberID, MemberApplicationDataID
   FROM HR.MemApplicationData
   WHERE MemberID=42;

   MemberID              MemberApplicationDataID
   -------------------   -----------------------
   42                    1001

5. Delete the listed child row(s) matching the Parent PK and then

   retry to delete the parent record.
   If you still cannot delete perform above steps, going down the rabbit
   hole of related tables, until you can.
   DELETE FROM HR.MemApplicationData
   WHERE MemberApplicationID=1001;

APPENDIX: fk_ForTable.sql

-- fk_ForTable.sql
-- Purpose: Show all Foreign Keys (FK) for Table.

SET linesize 240
SET pagesize 9999
SET verify OFF

COL owner             FORMAT a10
COL constraint_name   FORMAT a35
COL r_owner           FORMAT a10
COL r_constraint_name FORMAT a25

-- Prompt
PROMPT +-----------------------------------------+
PROMPT | Display All Foreign Keys (FK) for Table |
PROMPT +-----------------------------------------+
PROMPT
ACCEPT v_Schema PROMPT 'Schema [HR]:     ' DEFAULT 'HR'
ACCEPT v_Table  PROMPT 'Table [MEMBERS]: ' DEFAULT 'MEMBERS'
PROMPT

-- Process
SELECT owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
FROM all_constraints 
WHERE constraint_type='R'
AND   r_constraint_name in (select constraint_name 
                            from all_constraints 
                            where constraint_type in ('P','U') 
                            and table_name='&v_Table')
AND   owner = '&v_schema';

-- END

<- TroubleShooting