Thread: Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT...

I cannot do this pair of table creations directly, because they are mutually
dependent.


create table purchased_job
(supplier    char(10)    not null                    references supplier (id) match full,specification
text,del_point   char(2)        not null                    references location (id) match full,import_licence    bool
     default 'f',import_duty    numeric(12,2),terms        char(3),deliv_clear    numeric(12,2),
 
foreign key (product, supplier) references product_supplier (product, 
supplier) match full
)inherits (job)
;



create table product_supplier
(product        char(10)        not null                    references purchased_job (product) match full,supplier
char(10)   not null                    references supplier (id) match full,
 
primary key (product, supplier)
)
;

so I omitted the foreign key specification from the creation of purchased_job
and tried to add it afterwards, but (after fixing a bug in gram.y) I found
that ALTER TABLE ... ADD CONSTRAINT is not yet implemented.  Is there, then, any
way to create this mutual dependency?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "And, behold, I come quickly; and my reward is
withme,     to give every man according as his work shall be."                                  Revelation 22:12 
 

On Mon, 17 Jan 2000, Oliver Elphick wrote:

> I cannot do this pair of table creations directly, because they are mutually
> dependent.
> 

I don't think this will ever work. I can't really decode your intentions
here but I recall that translating proper relational schemas (you know,
the ones with the bubbles and lines) into tables never creates this sort
of situation. Then again I could be wrong.

> 
> create table purchased_job
> (
>     supplier    char(10)    not null
>                         references supplier (id) match full,
>     specification    text,
>     del_point    char(2)        not null
>                         references location (id) match full,
>     import_licence    bool        default 'f',
>     import_duty    numeric(12,2),
>     terms        char(3),
>     deliv_clear    numeric(12,2),
> 
>     foreign key (product, supplier) references product_supplier (product, 
> supplier) match full
> )
>     inherits (job)
> ;
> 
> 
> 
> create table product_supplier
> (
>     product        char(10)        not null
>                         references purchased_job (product) match full,
>     supplier    char(10)    not null
>                         references supplier (id) match full,
> 
>     primary key (product, supplier)
> )
> ;
> 
> so I omitted the foreign key specification from the creation of purchased_job
> and tried to add it afterwards, but (after fixing a bug in gram.y) I found
> that ALTER TABLE ... ADD CONSTRAINT is not yet implemented.  Is there, then, any
> way to create this mutual dependency?

Thanks for that fix, that was me changing the grammar for an ALTER TABLE /
ALTER COLUMN implementation, which now works btw.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut wrote: >On Mon, 17 Jan 2000, Oliver Elphick wrote: > >> I cannot do this pair of table creations
directly,because they are mutual     >ly >> dependent. >>  > >I don't think this will ever work. I can't really decode
yourintentions >here but I recall that translating proper relational schemas (you know, >the ones with the bubbles and
lines)into tables never creates this sort >of situation. Then again I could be wrong.
 
The idea is that suppliers of products can only supply products that are
purchased, rather than manufactured; and purchased products must have
suppliers.  However, it is possible for there to be more than one potential
supplier of a product; the one listed in purchased_jobs is the currently
favoured supplier.

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.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "And, behold, I come quickly; and my reward is
withme,     to give every man according as his work shall be."                                  Revelation 22:12 
 

"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.
        regards, tom lane

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) #

Jan Wieck wrote:
> 
>      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.
> 

Oracle solves these kind of problems by having a CREATE OR REPLACE command, 
that keeps as much of related objects as possible if there is already an 
object by that name.

Does anyone know if it is ANSI SQL ?

--------------------------
Hannu

On 2000-01-17, Jan Wieck mentioned:

>      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.

My priority is actually ALTER TABLE / DROP COLUMN, at least in a crude
'use at your own risk, all your defaults and constraints are gone' way if
I can't figure it out better by then. The ALTER TABLE / ALTER COLUMN /
SET|DROP DEFAULT was just a by-product.

I have been looking into all this ALTER TABLE code (or at least similar 
code which it would have to peruse, since there is not a lot of ALTER
TABLE code) and I think I have a good understanding of what would need to
happen, but I don't think I want to risk that now. (After all, I just
_think_ I understand it.) We'll see.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden