Обсуждение: 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Robert Sent: Wednesday, July 02, 2003 12:44 PM To: pgsql-general@postgresql.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@gmx.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