Re: Adding "on delete cascade" after table creation ?

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(at)obelix(dot)wu-wien(dot)ac(dot)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(at)megazone23(dot)bigpanda(dot)com]
>> Gesendet: Donnerstag, 19. September 2002 17:06
>> An: Peter Alberer
>> Cc: pgsql-general(at)postgresql(dot)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