Update constraint

Toad for Oracle & DevOps Toolkit
1

There is no possibility to update a Foreigh key constraint to another update setting eg "Cascade Delete" once it is created in "On Delete set null"

2

Hi Rob,

I don't follow you. Can you provide a little more detail?

Thanks

3

image
image936×665 35.4 KB

In this screen it is not possible to update the behaviour of the foreign key

image

When you create a foreign key you can define how the constraint should work

4

I see that, thank you.

I think it's by design that the alter constraint dialog doesn't have that...that one is designed to support multiple constraints, and since this property only applies to FK's it was excluded.

The "Alter Table" window does let you modify this property, but I see that it isn't generating SQL when you do that. I have a note in the source about the data dictionary not being consistent. That's probably a very old note, so I've logged this to re-evaluate for modern Oracle versions.

5

Ok I checked PL/SQL Developer and there it possible
Also SQL Developer can handle this

image
image920×264 12.9 KB

6

Thanks. It should be an easy fix.

7

As it turns out, there is no SQL that can modify the constraint in this way.

Those other tools drop and recreate the constraint to achieve this.

There is still a bug in Toad's Alter table window, in that the "on delete" controls are enabled when they should not be. If I add this to Toad, I'll have to make it clear that the constraint will be dropped and then re-added. Some users might not like this happening w/o notice.