Thread: changing referential integrety action on existing table

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

On Thu, 6 Feb 2003, Matthew Nuzum wrote:

> 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 think the best way is to drop all three triggers for the constraint and
use alter table add constraint to add the constraint again withe the
options you want.