WorkspaceMan & constraints of versioned tables

[Zahier]

Hello again Techies,

I suspect a bug in my Workspace Manager. When the spatial support person secures the tables for versioning, it does not allow you to alter the constraints on the table. This is expected behaviour. So Oracle provides packages to alter these versioned tables via a "temp" table using(DBMS_WM.beginDDL), but even after doing so I cannot alter the constraint, in fact now we can't even see the constraint, although it does indeed exist. Confirmed in SYS.ALL_WM_RIC_INFO.

Example;
He gets this when doing an update on the version-enabled table, 'RIVERS'.
ORA-02291: integrity constraint violated - parent key not found
As a result he wants to disable the constraint. So runs this.
execute DBMS_WM.BeginDDL('RIVERS'); ## This creates the temp table, RIVERS_LTS ##
alter table RIVERS_LTS disable constraint INDX_10_RIVER_FK;
ORA-02431: cannot disable constraint (INDX_10_RIVERS_FK) - no such constraint

I've been reading this,

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14253/long_intro.htm#i1009427

and on Metalink there are no hits for such a problem existing. Worth noting I am running 10.2.0.4.4 of WorkspaceMan on a 10.2.0.4 32bit install on a Suse Linux OS.

Has anyone with experience in WorkspaceMan come across such a scenario?

 

[hoinz]

A striking difference:
alter table RIVERS_LTS disable constraint INDX_10_RIVER_FK;
ORA-02431: cannot disable constraint (INDX_10_RIVERS_FK)

with or without S ?
a typo ?

 

[Zahier]

Definitely a typo in the post, hoinz.

We have found this in the 10gR2 documentation.

...you cannot add, drop, enable, or disable a referential integrity constraint that involves two tables if one table is version-enabled and the other is not version-enabled. Both tables must be version-enabled.

So it can't be done, however a work-around was found. Workspace Manager generates triggers for each table to manage the versioning. In other words, for the affected table called RIVERS, there are 3 system-generated triggers for each constraint on the table. All start with OVM_INSERT_xxx, OVM_UPDATE_xxx, OVM_DELETE_xxx, where xxx is a unique number.
So we find the triggers affecting the RIVERS table and disable these triggers, we can then disable the constraint, fix what we need, and then enable the constraint and then the triggers.