Re: How to determine the Oracle session's constraint state?
Date: Mon, 8 Feb 2010 09:27:41 -0800 (PST)
On Feb 8, 12:08 pm, "John Peterson" <j0..._at_comcast.net> wrote:
> First time poster in this forum -- please forgive me if this is the wrong
> place for my question. I've exhausted a web search on this issue, and was
> hoping that this might be a more targeted approach.
> I am trying to programmatically determine the current session's constraint
> state (immediate, deferred, or default).
> I have a procedure that I'd like to implement which would essentially
> temporarily set the constraints "deferred" (e.g. SET CONSTRAINTS ALL
> DEFERRED), perform some work, and then restore the constraint setting to the
> original state (e.g., immediate). However, I'm having a difficult time
> identifying how to determine the current state of the constraints.
> Any help would be very much appreciated!
> Kind regards,
> John Peterson
See the DEFERRABLE and DEFERRED columns in the ALL_, USER_, or DBA_CONSTRAINTS views (documented in the Oracle version# Reference manual).
By the way making a PK or UK deferrable would require use of a nonunique index to support the constraint instead of the standard unique index.
HTH -- Mark D Powell -- Received on Mon Feb 08 2010 - 11:27:41 CST