Re: URGENT: How to change ON CASCADE RESTRICT to DELETE?

On Wed, 2 Jul 2003, Nigel J. Andrews wrote:

> On Wed, 2 Jul 2003, Robert wrote:
>
> > Stephan Szabo wrote:
> >
> > >>  how can I change ON CASCADE RESTRICT to ON CASCADE DELETE? I need to
> > >>do it on my production database now (client is really complaining...)
> > >>and I dont see any DROP CONSTRAINT in the docs - whats the right and/or
> > >>simplest way? Thanks a lot
> > >>
> > >>
> > >
> > >If you're using 7.3, you should IIRC be able to use alter table to drop
> > >the constraint and then re-add it.
> > >
> > >If you're using an older version, I think you may need to find the
> > >triggers involved and drop those and then re-add the constraint.  I think
> > >techdocs has some info on finding the triggers.
> > >
> > >
> > Tried ALTER TABLE (yes, this is 7.3) but \d says
> >
> > nbcz=# \d seasons
> >                             Table "public.seasons"
> >   Column  |  Type   |
> > Modifiers
> > ----------+---------+---------------------------------------------------------
> >  id       | integer | not null default
> > nextval('public.seasons_id_seq'::text)
> >  hotel_id | integer |
> >  name     | text    |
> > Indexes: seasons_pkey primary key btree (id)
> > Foreign Key constraints: $1 FOREIGN KEY (hotel_id) REFERENCES hotels(id)
> > ON UPDATE NO ACTION ON DELETE NO ACTION
> >
> > and
> >
> > ALTER TABLE seasons DROP CONSTRAINT $1;
> >
> > didn't work. Apparently I'm more then a bit confused, but what's the
> > name of the constraint here? I finally took a deep breath, dropped the
> > database and edited dump directly. Now it seems to work, but I'd still
> > like to now the correct way (ALTER TABLE but how?) Thanks for your help
> >
>
> I don't know about it being the correct way but isn't there entries in
> pg_constraint that you can twiddle directly to change the cascade action?
>

I don't think this is correct.  I believe the function that the
referential integrity trigger points to (pg_trigger.tgfoid) is what
actually determies the RI action.  The pg_constraint entry is just a
label of the action when it was constructed.

Kris Jurka