URGENT: How to change ON CASCADE RESTRICT to DELETE?

Hi,

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

- Robert

Drop constraint is there within Alter Table command.

Regards,
Kallol.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Robert
Sent: Wednesday, July 02, 2003 12:44 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] URGENT: How to change ON CASCADE RESTRICT to DELETE?

Hi,

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

- Robert

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Wed, 2 Jul 2003, Robert wrote:

> Hi,
>
> 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.

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

- Robert

> 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?

ALTER TABLE seasons DROP CONSTRAINT "$1";

Ian Barwick
barwick(at)gmx(dot)net

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?

--
Nigel J. Andrews

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