FOREIGN KEY migration of syntax, help needed

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
Thread:
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ralph Smith 2007-07-31 19:27:39 Re: Auto Starting +/or Shutdown on OS X
Previous Message Robert Landrum 2007-07-31 19:15:27 Indexing Hostnames with tsearch2 and fti.c