This discussion is archived
3 Replies Latest reply: Aug 28, 2012 2:35 AM by stuartu RSS

Why integrity constraint error after child records already deleted?

stuartu Explorer
Currently Being Moderated
I'm running Oracle 11.2.0.3 on RHEL 5.7.

I have two tables....
- HORIZON.PROGRAM_CONTENT (parent) - not version enabled
- HORIZON.OUTBOX (child) - version enabled

I want to clear out both of these tables, so I first remove the child records, then parent records, but the delete on the parent fails...
DEV: HORIZON > select * from user_wm_ric_info where ric_name = 'FK_OUTBOX_PROGRAM_CONTENT_ID';

CT_OWNER   CT_NAME  PT_OWNER   PT_NAME            RIC_NAME                     CT_COLS           PT_COLS  R_CONSTRAINT_NAME                  DELETE_RULE  STATUS
---------- -------- ---------- --------------- ---------------------------------------- -------------------- -------- ---------------------------------------- ------------ --------
HORIZON    OUTBOX   HORIZON    PROGRAM_CONTENT FK_OUTBOX_PROGRAM_CONTENT_ID          PROGRAM_CONTENT_ID   ID       PROGRAM_CONTENT_ID_PK                 R         ENABLED

1 row selected.

DEV: HORIZON > select count(*) from program_content;

  COUNT(*)
----------
       904

1 row selected.

DEV: HORIZON > select count(*) from outbox where program_content_id in (select id from program_content);

  COUNT(*)
----------
      3052

1 row selected.

DEV: HORIZON > delete from outbox;

3089 rows deleted.

DEV: HORIZON > delete from program_content;
delete from program_content
            *
ERROR at line 1:
ORA-20005: integrity constraint (HORIZON.FK_OUTBOX_PROGRAM_CONTENT_ID) violated - child record found
ORA-06512: at "WMSYS.WM_ERROR", line 324
ORA-06512: at "WMSYS.WM_ERROR", line 348
ORA-06512: at "HORIZON.LT_AD_19", line 27
ORA-04088: error during execution of trigger 'HORIZON.LT_AD_19'
I look at the trigger code on this table....
CREATE OR REPLACE TRIGGER "HORIZON"."LT_AD_19" AFTER
  DELETE ON HORIZON.PROGRAM_CONTENT FOR EACH ROW DECLARE dummy INTEGER;
  dependent_rows  BOOLEAN;
  ricLockStatus   INTEGER;
  dummyLockStatus INTEGER;
  BEGIN
    wmsys.lt_ctx_pkg.initializeRicLockingVars;
    ricLockStatus     := wmsys.lt_ctx_pkg.request(10000005,3, wmsys.lt_ctx_pkg.MAXWAIT, true);
    IF ( ricLockStatus = 0 ) THEN
      wmsys.lt_ctx_pkg.addToRicLocksList( 'HORIZON.PROGRAM_CONTENT', 'RE' );
    ELSE
      IF ( ricLockStatus = 4 ) THEN
        IF ( wmsys.lt_ctx_pkg.hasRicLockOn('HORIZON.PROGRAM_CONTENT', 'S')) THEN
          ricLockStatus      := wmsys.lt_ctx_pkg.request(10000006, 6, 0, true);
          IF ( ricLockStatus != 0 AND ricLockStatus != 4 ) THEN
            WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_171_NO, 'deadlock detected while trying to acquire lock on HORIZON.PROGRAM_CONTENT');
          END IF;
          ricLockStatus      := wmsys.lt_ctx_pkg.convert(10000005, 6, wmsys.lt_ctx_pkg.MAXWAIT);
          IF ( ricLockStatus != 0 ) THEN
            dummyLockStatus  := wmsys.lt_ctx_pkg.release(10000006);
            WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_171_NO, 'error while trying to acquire lock on HORIZON.PROGRAM_CONTENT, status = ' || ricLockStatus );
          END IF;
        END IF;
      ELSE
        WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_171_NO, 'error while trying to acquire lock on HORIZON.PROGRAM_CONTENT, status = ' || ricLockStatus );
      END IF;
    END IF;
    BEGIN
      dependent_rows := true;
      SELECT 1
      INTO dummy
      FROM dual
      WHERE EXISTS
        (SELECT 1
        FROM HORIZON.OUTBOX_LT
        WHERE ((:OLD.ID = PROGRAM_CONTENT_ID))
        AND delstatus  >= 0
        );
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dependent_rows := false;
    WHEN OTHERS THEN
      Raise;
    END;
    BEGIN
      IF (dependent_rows) THEN
        WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_5_NO, 'HORIZON', 'FK_OUTBOX_PROGRAM_CONTENT_ID');
      END IF;
    END;
  END;
  /
In the same session I query,....
DEV: HORIZON > select delstatus, count(*) 
               from outbox_lt
               where program_content_id in (select id from program_content)
               and delstatus  >= 0
               group by delstatus;

 DELSTATUS   COUNT(*)
---------- ----------
     11         4
     13         1
     14         1
     10      3052
     12         1
     16         1
     15         1

7 rows selected.
Can someone explain this error and how to resolve it?
  • 1. Re: Why integrity constraint error after child records already deleted?
    aschilling Newbie
    Currently Being Moderated
    hi there!

    the reason is pretty simple: your child table is versioned and thus it references the parent table in the past.
    You always have to think about that it might be possible that you travel back in time with gotoSavepoint() or the like and thus your parent table entries still need to be there. So it makes sense that you can't delete them.
    That's why one should be very clear about what data an unversioned parent table contains, a rule of thumb could be that it's "add only" but never "delete".
    While it can be tempting to have it unversioned to avoid data management overhead when having several/many workspaces it can cause problems like that.

    regards,

    Andreas
  • 2. Re: Why integrity constraint error after child records already deleted?
    stuartu Explorer
    Currently Being Moderated
    Thanks. So your saying I should just version enable the parent? This is more an oversight than intentional, but only a dev environment at the moment.

    Edited by: stuartu on Aug 28, 2012 7:51 PM
  • 3. Re: Why integrity constraint error after child records already deleted?
    stuartu Explorer
    Currently Being Moderated
    Now that I've had time to try, yes, version enabling the parent did work.
    DEV: HORIZON > exec dbms_wm.enableversioning('PROGRAM_CONTENT','VIEW_WO_OVERWRITE');
    
    PL/SQL procedure successfully completed.
    
    DEV: HORIZON > delete from outbox;
    `
    4210 rows deleted.
    
    DEV: HORIZON > delete from program_content;
    
    904 rows deleted.
    
    DEV: HORIZON > 
    This schema will contain research data when it gets into Production. The quality of data is important and we need audit trails for everything. There will be times when we need to delete data, but probably won't be on the scale of what our developer wanted here (to clear out the tables).

    OWM seems to be a great fit for this, and certainly is better than our current approach of custom triggers.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points