Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Implementing Constraints

Re: Implementing Constraints

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 1 Jul 2001 21:25:33 +1000
Message-ID: <3b3f08ec@news.iprimus.com.au>

"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
>
>
Received on Sun Jul 01 2001 - 06:25:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US