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