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.

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

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:

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