Re: How to determine the Oracle session's constraint state?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 8 Feb 2010 09:27:41 -0800 (PST)
Message-ID: <63f31d18-8229-43b2-9fbb-1c79cb5bdefc_at_l26g2000yqd.googlegroups.com>

On Feb 8, 12:08 pm, "John Peterson" <j0..._at_comcast.net> wrote:
> Hello!
>
> 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