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