Problem with ON CASCADE RESTRICT in Oracle 11g


We’re investigating migrating to using Visual Paradigm for our DB modeling tool, and so far we’re quite pleased with what it offers.

We’re using Oracle 11g and we’re running into the following issue when generating DDLs from a previously imported model:

All the imported relationships have the “On Update” and “On Delete” options checked (with cascade for update and restrict for delete). When generating the schema from VP, the alter statements skip the on update part (which is good, since it’s not valid in Oracle) but include the on delete option (the statement specifies “ON DELETE Restrict”). Now this might have been an option in earlier versions of Oracle, but our version complains about restrict not being among the accepted values (only cascade or set null are valid options, as seen here

First off, we think this is a bug (or a missing feature, where the version of oracle can be specified), an we can submit a bug report if you agree. Or let us know if there is a way to accomplish this, and we missed it.

Second, aside from getting a new build that covers this, is there a way to change the on delete flags in bulk, on all the relationships? Some of the DBs we’re working with have lots of relationships, and having to manually update this for each one of them will be very time consuming, so we’re hoping there’s an easier way to accomplish this.

Thanks in advance.