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

Tom Lane wrote:

> "Oliver Elphick" <olly(at)lfix(dot)co(dot)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/or UPDATE 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 regular trigger will never
work for that!

In the mean time, you can setup the same RI triggers by hand using
CREATE CONSTRAINT TRIGGER with the appropriate builtin RI_FKey
functions. These commands are exactly what ALTER TABLE has to issue.
The functions are named RI_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 referencing tables 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 up to now :-( ), you can
specify deferrability and initial deferred state for the trigger. And
it correctly sets up the 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 dropping and 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.c how 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(at)debis(dot)com (Jan Wieck) #