Thread: FOREIGN KEY migration of syntax, help needed

Hi,

   Quick question:

   My old database has the old-style FOREIGN KEY syntax:

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER INSERT OR UPDATE ON assettype
    FROM assettype
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'assettype', 'assettype', 'UNSPECIFIED', 'pid', 'id');

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER DELETE ON assettype
    FROM assettype
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_cascade_del"('<unnamed>', 'assettype', 'assettype', 'UNSPECIFIED', 'pid', 'id');

CREATE CONSTRAINT TRIGGER "<unnamed>"
    AFTER UPDATE ON assettype
    FROM assettype
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_cascade_upd"('<unnamed>', 'assettype', 'assettype', 'UNSPECIFIED', 'pid', 'id');


   I was wondering if I need to worry about the RI_FKey_check_ins statement.

   Will the following take care of all three statements?

   ALTER TABLE ONLY assettype
   ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id)
   ON UPDATE CASCADE ON DELETE CASCADE;


    Are there any other gottcha's when doing this type of migration?
(I need to use the FKEY syntax, so a schema visualizer will show the
foreign key relationships).


   thanks a ton,


   mike

Mike Haberman <mikeh@ncsa.uiuc.edu> writes:
>    Will the following take care of all three statements?

>    ALTER TABLE ONLY assettype
>    ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id)
>    ON UPDATE CASCADE ON DELETE CASCADE;

Yes, there are three or so triggers under the hood of any FOREIGN KEY
constraint.

            regards, tom lane

Thank you for the quick response.

   If all my old constraints are NOT DEFERRABLE INITIALLY IMMEDIATE
    does that mean I don't have to worry about the deferrable keyword?

   mike


On Tue, Jul 31, 2007 at 04:00:59PM -0400, Tom Lane wrote:
> Mike Haberman <mikeh@ncsa.uiuc.edu> writes:
> >    Will the following take care of all three statements?
>
> >    ALTER TABLE ONLY assettype
> >    ADD CONSTRAINT at_fk_1 FOREIGN KEY (pid) REFERENCES assettype(id)
> >    ON UPDATE CASCADE ON DELETE CASCADE;
>
> Yes, there are three or so triggers under the hood of any FOREIGN KEY
> constraint.
>
>             regards, tom lane

--
-----------------------------------------------------------------------
Mike Haberman
Senior Software/Network Research Engineer
National Center for Supercomputing Applications
217.244.9370
-----------------------------------------------------------------------

Le mardi 31 juillet 2007, Mike Haberman a écrit :
>    My old database has the old-style FOREIGN KEY syntax:

I've had this very same transition to make on a database here, and
successfully used adddepend:
  http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/adddepends/adddepends/

It has been moved out from contrib to pgfoundry as of 8.2, but on a 8.1 server
where the constraint triggers seem to have been inherited from 7.x times, was
darn usefull.

Hope this helps,
--
dim

wow.. Thank you.

mike


On Wed, Aug 01, 2007 at 10:31:16AM +0200, Dimitri Fontaine wrote:
> Le mardi 31 juillet 2007, Mike Haberman a ?crit?:
> >    My old database has the old-style FOREIGN KEY syntax:
>
> I've had this very same transition to make on a database here, and
> successfully used adddepend:
>   http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/adddepends/adddepends/
>
> It has been moved out from contrib to pgfoundry as of 8.2, but on a 8.1 server
> where the constraint triggers seem to have been inherited from 7.x times, was
> darn usefull.
>
> Hope this helps,
> --
> dim



--
-----------------------------------------------------------------------
Mike Haberman
Senior Software/Network Research Engineer
National Center for Supercomputing Applications
217.244.9370
-----------------------------------------------------------------------