This discussion is archived
5 Replies Latest reply: Nov 21, 2011 12:15 PM by Ben Speckhard RSS

Automated Solution for Integrity Constraints?

33450 Newbie
Currently Being Moderated
Here is the situation. I have a parent table with child rows in LIVE. A workspace is then created, and another child is added to one of the parent rows in live. Another workspace is then created, and the parent row for the first workspaces child is deleted. The user tries to merge the second workspace to LIVE, which fails with a ORA-20237 error (expected).

How do I in an automated fashion, determine what row(s) in what workspace(s) are causing the error workspace merge? This is on 10gR2.

Integrity constraint violations do not show up in the CONF views, so that doesn’t help. I need to be able to eliminate the conflicts in an automated fashion, providing notice to the owners of the workspaces that they need to go revise the parts that were forcefully removed. But to do that, I need to know how to locate the issues causing the problem so I can build a package to do this, hands off.

Example script:
SQL> create table test_parent (pk number not null, name varchar2(50),
  2   CONSTRAINT test_parent_ndx PRIMARY KEY (pk));

Table created.

SQL> create table test_child (pk number not null,  fk number, name varchar2(
  2    CONSTRAINT test_child_ndx PRIMARY KEY (pk),
  3    CONSTRAINT test_child_FK01
  4   FOREIGN KEY (fk)
  5   REFERENCES test_parent (pk)
  6   on delete cascade);

Table created.

SQL> exec dbms_wm.enableversioning('test_parent,test_child');

PL/SQL procedure successfully completed.

SQL> insert into test_parent values (1, 'First item');

1 row created.

SQL> insert into test_parent values (2, 'Second item');

1 row created.

SQL> insert into test_child values (101, 1, 'References first item');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item


SQL> exec DBMS_WM.createworkspace('1', TRUE, 'None',  TRUE );

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.gotoworkspace('1');

PL/SQL procedure successfully completed.

SQL> insert into test_child values (102, 1, 'References first item');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item
       102          1 References first item

SQL> exec DBMS_WM.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.createworkspace('2', TRUE, 'None',  TRUE );

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.gotoworkspace('2');

PL/SQL procedure successfully completed.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item

SQL> delete from test_parent where pk = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         2 Second item

SQL> select * from test_child;

no rows selected

SQL> exec DBMS_WM.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.mergeworkspace('2', FALSE, FALSE, FALSE  );
BEGIN DBMS_WM.mergeworkspace('2', FALSE, FALSE, FALSE  ); END;

*
ERROR at line 1:
ORA-20237: integrity constraint (CVC.TEST_CHILD_FK01) violated in workspace
LIVE or one of its descendants - child record found
ORA-06512: at "WMSYS.LT", line 6009
ORA-06512: at line 1
Bryan
  • 1. Re: Automated Solution for Integrity Constraints?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Bryan,

    You could probably do something along the lines of the following:

    1. Identify which rows were deleted from the workspace in which you are merging by using the _diff view.

    2. For each of those rows (or possibly in bulk if possible), step though each of the workspaces looking for possible child rows that could violate the constraint. The _mw view could possibly be used to query multiple workspaces at the same time.

    In the scenario you described, you would only need to query rows from child workspaces which were continually refreshed. Additionally, if the constraint was not 'on delete cascade', then you would need to include the parent workspace, in this case LIVE.

    Also, the constraint checking is done for all points/savepoints within the child workspaces, so there may be cases when in order to resolve the constraint violation, the additional child rows in the child workspace would need to be rolled back or removed.

    Regards,
    Ben
  • 2. Re: Automated Solution for Integrity Constraints?
    33450 Newbie
    Currently Being Moderated
    Ben,

    Some background here. As with this example, all workspaces are now CR (as we found non-CR workspaces too confusing for the users). All workspaces are single-parent.

    1. Yes, I understand how the _diff views can be used here, makes sense and works fine.

    2. Unfortunately the mw views are worthless here, as they are not multiparent workspaces so the mw views are empty.

    So, what we are working on now is a view to look at the LT table, and include just the latest version for each workspace, along with the workspace name. This view will make finding the FK child dependency (of the _diff view items) easier to find, without going through every workspace (SLOW).

    Bryan
  • 3. Re: Automated Solution for Integrity Constraints?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Bryan,

    The mw view does not necessarily need to be used with multiparent workspaces.  You can specify the workspaces you are interested in by using the dbmswm.SetMultiWorkspaces procedure.

    Regards,
    Ben
  • 4. Re: Automated Solution for Integrity Constraints?
    900873 Newbie
    Currently Being Moderated
    Ben,
    I am having the same issue. I have LIVE and workspace1 created from LIVE. Workspace1 is continuously refreshed workspace. When we delete a record in workspace1 and merge it back to live we get

    ORA-20237: integrity constraint (T2.CAMPAIGN_INSTRUCTION_FK1) violated in workspace LIVE or one of its descendants - child record found .

    I am not follwoing your solution. Ideally Merge should result in the deletion of the record from LIVE and any other workspace (say w2which is continously refreshed and created from LIVE). If the same record is modified in w2 then we should see that record to be in conflict after w1 merges.

    If i set Continous refresh to FALSE then the merge works correctly and workspace w2 shows up conflicts. I have to use CR. Please help us in understanding how to work around the integrity constraint issues.
    Thank you
    Amit Gangwar

    Edited by: 897870 on Nov 17, 2011 3:30 PM

    Edited by: 897870 on Nov 17, 2011 3:48 PM
  • 5. Re: Automated Solution for Integrity Constraints?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Amit,

    The merge operation does result in the deletion of the row from both LIVE and the child workspace. However, this would cause the child table in the other (non-merged) workspace to have data without a corresponding parent row. We can not allow this, and so raise a referential integrity constraint violation. It is also not a conflict, as conflicts are always for rows with the same primary key, not for business conflicts or constraint violations. In the example, the inserted row in the child workspace was for a primary key that was unique within all workspaces, and so no conflict violation would be raised.

    The reason it works for a non-CR workspace, is that the parent table row is still visible from the other workspace as it was not automatically deleted.

    You can use the DIFF view(with SetDiffVersions) or the MW view(with SetMultiWorkspaces) to determine which rows were deleted from the parent table that you are merging. This information can then be used to find the other workspace/savepoint that is causing the violation and fix it. This could be done by removing the child row or inserting/updating the parent row prior to the other workspace being merged.

    Ben