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



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