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

From: Mark D Powell <>
Date: Mon, 8 Feb 2010 09:27:41 -0800 (PST)
Message-ID: <>

On Feb 8, 12:08 pm, "John Peterson" <> 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

Original text of this message