Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT...
От | Jan Wieck |
---|---|
Тема | Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT... |
Дата | |
Msg-id | 38835F06.9C0B7FDF@debis.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT... (Peter Eisentraut <e99re41@DoCS.UU.SE>) |
Ответы |
Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE...
ADD CONSTRAINT...
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-hackers |
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) #
В списке pgsql-hackers по дате отправления: