Re: Implementing Constraints

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Sat, 21 Jul 2001 21:22:58 GMT
Message-ID: <3B40CA1A.FCB333FC@attws.com>

"Howard J. Rogers" wrote:

> "Victor" <lch_1_at_hotmail.com> wrote in message
> news:9hmg7e$7qh41_at_imsp212.netvigator.com...
> > Dear all,
> >
> > In the oracle book, i found the following syntax in defining the
 constraint:
> >
> > column dataytpe {CONSTRAINT constraint]
> > in_line_constraint
> > [defer_spec]
> > in_line_constraint :==
> > {[NOT] NULL
> > | PRIMARY KEY
> > .
> > .
> > .
> > defer_spec :==
> > [NOT DEFERRABLE | DEFERRABLE [INITIALLY {IMMEDIATE | DEFERRED}]
> > .
> > .
> >
> > I don't understand why the DEFERABLE syntax can be used with the IMMEDIATE
> > syntax.

>

> Because, in plain English, there is a difference between something which is
> 'deferrable' and something which is actually 'deferred'. Deferrable just
> means that, one day, you might choose to defer the constraint. It doesn't
> mean it is necessarily deferred at this precise moment.

>


> >From the book, the use of IMMEDIATE syntax indicate that the default

> > is to check this constraint at the end of every DML statement. But

> > DEFFERABLE means check when COMMIT.

> > It makes me confuse
> >

>

> No, deferrable simply means that you have the option to make the constraint
> deferred. A deferred constraint (ie, one that is *actually* deferred, right
> this second) is indeed only checked at the time the commit statement is
> issued. By contrast an immediate constraint is checked for each and every
> line of DML, as it is submitted.

>

> What that means in practice is that, if you were loading 100,000 rows,
> instead of checking the constraint 100,000 times (immediate), you do a giant
> check just once (deferred). Naturally, that makes the load itself go much
> quicker. On the other hand, if one of the 100,000 rows violates the
> constraints, an immediate constraint would simply result in that one row
> being rejected. With a deferred constraint, all 100,000 rows would be
> rolled back out of the database.

>

> So, if you like the sound of deferred constraints, then you must specify
> them as being 'deferrable'. A 'nondeferrable' constraint can NEVER be
> deferred. A deferrable one can be either immediate or deferred (ie, you
> have the option to actually defer it, but can choose not to do so).

>

> Regards
> HJR

>


> > THX

> >
> >

And having worked with these and grown to dislike them it also means you need huge rollback segments because since you can not doing incremental commits you must hold the entire load.

Daniel A. Morgan Received on Sat Jul 21 2001 - 16:22:58 CDT