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

Adding "on delete cascade" after table creation ?

From
"Peter Alberer"
Date:
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


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

From
Stephan Szabo
Date:
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.



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

From
"Peter Alberer"
Date:
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.
>



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

From
Tino Wildenhain
Date:
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



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

From
Stephan Szabo
Date:
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>")


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

From
Tom Lane
Date:
"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