Thread: Adding "on delete cascade" after table creation ?
Hi there,
i have a table that SHOULD look like this:
create table lr_object_usage (
lr_object_usage_id integer
constraint
lr_object_usage_lr_object_usage_id_pk
primary key,
lr_object_id integer
constraint lr_object_usage_lr_object_id_fk
references lr_objects(lr_object_id)
on delete cascade,
access_time timestamp,
user_id integer
constraint lr_object_usage_user_id_fk
references users(user_id),
status varchar(11),
context integer
constraint lr_object_usage_context_fk
references
lr_object_usage(lr_object_usage_id)
___ON DELETE CASCADE___
);
unfortunately i think that the table was created without the delete
cascade in the last line (for the constraint
lr_object_usage_context_fk). Is it somehow possible to add the delete
cascade without recreating the table?
TIA, peter
On Thu, 19 Sep 2002, Peter Alberer wrote: > Hi there, > > i have a table that SHOULD look like this: > > create table lr_object_usage ( > lr_object_usage_id integer > constraint > lr_object_usage_lr_object_usage_id_pk > primary key, > lr_object_id integer > constraint lr_object_usage_lr_object_id_fk > references lr_objects(lr_object_id) > on delete cascade, > access_time timestamp, > user_id integer > constraint lr_object_usage_user_id_fk > references users(user_id), > status varchar(11), > context integer > constraint lr_object_usage_context_fk > references > lr_object_usage(lr_object_usage_id) > ___ON DELETE CASCADE___ > ); > > unfortunately i think that the table was created without the delete > cascade in the last line (for the constraint > lr_object_usage_context_fk). Is it somehow possible to add the delete > cascade without recreating the table? You'll probably have to manually remove the triggers for the foreign key constraint (see techdocs.postgresql.org's fk primers for info) and then use alter table add constraint.
From looking at the techdocs document i got the impression that I simply need to change the value of "tgfoid" in pg_trigger from "1654" (RI_FKey_noaction_del) to "1646" (RI_FKey_cascade_del). Is this correct? >-----Ursprüngliche Nachricht----- >Von: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] >Gesendet: Donnerstag, 19. September 2002 17:06 >An: Peter Alberer >Cc: pgsql-general@postgresql.org >Betreff: Re: [GENERAL] Adding "on delete cascade" after table creation ? > > >On Thu, 19 Sep 2002, Peter Alberer wrote: > >> Hi there, >> >> i have a table that SHOULD look like this: >> >> create table lr_object_usage ( >> lr_object_usage_id integer >> constraint >> lr_object_usage_lr_object_usage_id_pk >> primary key, >> lr_object_id integer >> constraint lr_object_usage_lr_object_id_fk >> references lr_objects(lr_object_id) >> on delete cascade, >> access_time timestamp, >> user_id integer >> constraint lr_object_usage_user_id_fk >> references users(user_id), >> status varchar(11), >> context integer >> constraint lr_object_usage_context_fk >> references >> lr_object_usage(lr_object_usage_id) >> ___ON DELETE CASCADE___ >> ); >> >> unfortunately i think that the table was created without the delete >> cascade in the last line (for the constraint >> lr_object_usage_context_fk). Is it somehow possible to add the delete >> cascade without recreating the table? > >You'll probably have to manually remove the triggers for the foreign >key constraint (see techdocs.postgresql.org's fk primers for info) and >then use alter table add constraint. >
Hi Peter, yes, this would work. (But I hope you have a backup if you fiddle with system tables ;) We were able to drop a reference to a not existent table this way. In 7.3 we can use ALTER TABLE ... DROP CONSTRAINT ... :)) Regards Tino --On Donnerstag, 26. September 2002 12:15 +0200 Peter Alberer <h9351252@obelix.wu-wien.ac.at> wrote: > From looking at the techdocs document i got the impression that I simply > need to change the value of "tgfoid" in pg_trigger from "1654" > (RI_FKey_noaction_del) to "1646" (RI_FKey_cascade_del). Is this correct? > >> -----Ursprüngliche Nachricht----- >> Von: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] >> Gesendet: Donnerstag, 19. September 2002 17:06 >> An: Peter Alberer >> Cc: pgsql-general@postgresql.org >> Betreff: Re: [GENERAL] Adding "on delete cascade" after table creation > ? >> >> >> On Thu, 19 Sep 2002, Peter Alberer wrote: >> >>> Hi there, >>> >>> i have a table that SHOULD look like this: >>> >>> create table lr_object_usage ( >>> lr_object_usage_id integer >>> constraint >>> lr_object_usage_lr_object_usage_id_pk >>> primary key, >>> lr_object_id integer >>> constraint > lr_object_usage_lr_object_id_fk >>> references lr_objects(lr_object_id) >>> on delete cascade, >>> access_time timestamp, >>> user_id integer >>> constraint lr_object_usage_user_id_fk >>> references users(user_id), >>> status varchar(11), >>> context integer >>> constraint lr_object_usage_context_fk >>> references >>> lr_object_usage(lr_object_usage_id) >>> ___ON DELETE CASCADE___ >>> ); >>> >>> unfortunately i think that the table was created without the delete >>> cascade in the last line (for the constraint >>> lr_object_usage_context_fk). Is it somehow possible to add the delete >>> cascade without recreating the table? >> >> You'll probably have to manually remove the triggers for the foreign >> key constraint (see techdocs.postgresql.org's fk primers for info) and >> then use alter table add constraint. >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Thu, 26 Sep 2002, Peter Alberer wrote: > From looking at the techdocs document i got the impression that I simply > need to change the value of "tgfoid" in pg_trigger from "1654" > (RI_FKey_noaction_del) to "1646" (RI_FKey_cascade_del). Is this correct? Technically, yes I believe so (well, check to make sure the oids are those values on your system). I usually don't suggest manual surgery to the system tables as messing up is somewhat painful (even in dropping the triggers I'd suggest using drop trigger "<name>")
"Peter Alberer" <h9351252@obelix.wu-wien.ac.at> writes:
> From looking at the techdocs document i got the impression that I simply
> need to change the value of "tgfoid" in pg_trigger from "1654"
> (RI_FKey_noaction_del) to "1646" (RI_FKey_cascade_del). Is this correct?
Probably, but also keep an eye on the tgtype and tgargs. I'd recommend
creating two dummy tables with the right kind of foreign-key constraint,
and then looking at how their triggers are configured.
regards, tom lane