Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT...

Tom Lane wrote:

> "Oliver Elphick" <olly@lfix.co.uk> writes:
> > I guess I will have to remove the restriction that products listed in
> > product_suppliers must be purchased; it may indeed become possible for the
> > to change status from time to time, so that is not too unsatisfactory.
>
> You could possibly enforce dependencies like that by using a trigger
> function, instead of foreign-key stuff.
    In fact, ALTER TABLE ADD CONSTRAINT should do it!
    It's absolutely legal and makes sense in some case. The constraints    must be deferrable then, and you must INSERT
and/orUPDATE both rows    referring to each other in the same transaction while the constraints    are in deferred
state.
    A normal trigger is never deferrable, so it will be fired at the end    of the statement, not at COMMIT. Thus, a
regulartrigger will never    work for that!
 
    In the mean time, you can setup the same RI triggers by hand using    CREATE CONSTRAINT TRIGGER with the
appropriatebuiltin RI_FKey    functions. These commands are exactly what ALTER TABLE has to issue.    The functions are
namedRI_FKey_<action>_<event>, where <action> is    one of "check", "noaction", "restrict", "cascade", "setnull" or
"setdefault"and <event> is "ins", "upd" or "del". "check" has to be    used on the referencing table at INSERT and
UPDATE.The others are    for the PK table to issue the requested action. Don't forget to add    "noaction" for the
cases,where you don't want an action, otherwise    the deferred trigger queue manager will not notice if it has to
raise   the "triggered data change violation" exception.
 
    All RI_FKey functions take the following arguments:

       * The constraint name       * The match type (FULL for now)       * The primary key tables name       * The
referencingtables name       * Followed by pairs of PK-attrib, FK-attrib names.
 
    With CREATE CONSTRAINT TRIGGER (which I added first so someone could    already work on pg_dump - what noone does
upto now :-( ), you can    specify deferrability and initial deferred state for the trigger. And    it correctly sets
upthe PK<->FK tables relationships in pg_trigger,    so that DROPping one of them removes all the triggers using it
from   the other one. Needless to say that dropping and recreating a PK    table looses all the references! But
droppingand recreating the    referencing tables therefore doesn't put the PK table into an    unusable state.
 
    So Peter, if you're working on ALTER TABLE ADD CONSTRAINT, let it    setup the appropriate RI triggers. Look at
analyze.chow to do so.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #