From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: <pgsql-sql@postgresql.org>
Subject: changing referential integrety action on existing table
Date: Thu, 6 Feb 2003 14:25:43 -0500
Organization: Bearfruit.org
Message-ID: <006c01c2ce15$8a825eb0$6900a8c0@mattspc>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal

I have a table that uses the NO ACTION action for it's referential
integrity.  I'd like to change it to CASCADE for the ON DELETE event.
I'm using Postgres 7.2.

I noticed that in the output of my pg_dump I have some triggers that
look like:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTriger_*"

Is it possible to use this syntax to change the constraints?  The
documentation says it's not intended for general use and isn't very
detailed about it's use.

Here is an example from my dump file:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_43755" AFTER DELETE ON
"packages"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'components', 'packages',
'UNSPECIFIED', 'packageid', 'packageid');

Could I simply change the procedure mentioned from RI_FKey_noaction_del
to RI_FKey_cascade_del?

Do I have to do a DROP TRIGGER first?

I know I can just try it, but last time I got creative with this, it
cost me a couple hours trying to recreate things.

Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org