FOREIGN KEY: difference between NOT DEFERRABLE and INITIALLY IMMEDIATE

On Tue, 4 Sep 2001, 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.

Well, at start. With initially immediate you can use SET CONSTRAINTS to
change it on the fly back and forth from deferrable to not. NOT
DEFERRABLE means you cannot do so.

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@Yahoo.com #

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