Lists: | pgsql-general |
---|
From: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FOREIGN KEY migration of syntax, help needed |
Date: | 2007-07-31 19:19:49 |
Message-ID: | 20070731191949.GA43615@ncsa.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: FOREIGN KEY migration of syntax, help needed |
Date: | 2007-07-31 20:00:59 |
Message-ID: | 5943.1185912059@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)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
From: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: FOREIGN KEY migration of syntax, help needed |
Date: | 2007-07-31 20:34:56 |
Message-ID: | 20070731203456.GA44839@ncsa.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)ncsa(dot)uiuc(dot)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
-----------------------------------------------------------------------
From: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
Subject: | Re: {Spam} FOREIGN KEY migration of syntax, help needed |
Date: | 2007-08-01 08:31:16 |
Message-ID: | 200708011031.19022.dfontaine@hi-media.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
---|---|
To: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: {Spam} FOREIGN KEY migration of syntax, help needed |
Date: | 2007-08-01 16:04:20 |
Message-ID: | 20070801160420.GA62063@ncsa.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
-----------------------------------------------------------------------