Re: FOREIGN KEY: difference between NOT DEFERRABLE and INITIALLY

Gabriel Fernandez wrote:
> Hi,
>
> What's the difference between NOT DEFERRABLE and INITIALLY IMMEDIATE for
> a FOREIGN KEY specification ?
>
> It seems they are both defining the same: the moment the constraint will
> be checked: the instant the statement is processed or the end of
> transaction.
>
> Thanks,
>
> Gabi :-)

Not exactly.

DEFERRABLE means, that the application can issue a

SET CONSTRAINTS { <name_list> | ALL } { DEFERRED | IMMEDIATE }

whithin a transaction to change the actual behaviour of the
named or ALL deferrable constraints until either COMMIT or a
subsequent SET CONSTRAINTS. Setting a currently deferred
constraint to IMMEDIATE explicitly causes the so far
collected checks to be done at SET time.

INITIALLY { DEFERRED | IMMEDIATE } thus only controls the
initial state of the constraints checking behaviour at the
beginning of the transaction.

INITIALLY DEFERRED implicitly causes a constraint to be
DEFERRABLE.

Needless to say that NOT DEFERRABLE and INITIALLY DEFERRED
are mutually exclusive.

All this gives your application fine control about "when"
constraints get checked, while the database is still in full
charge of the referential integrity. If you have setup all
your constraints beeing INITIALLY DEFERRED, your application
can do the following:

BEGIN TRANSACTION;
-- do some stuff
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
-- do more stuff
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
-- do final stuff
SET CONSTRAINTS ALL IMMEDIATE;
COMMIT TRANSACTION;

The only places, where referential integrity errors can raise
now are the "SET ... IMMEDIATE" queries.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com