Обсуждение: Implementing cascading deletes on an existing table

Hi all,

Im trying to find out the best way to implement cascading deletes on an
existing table.  I have this:

create table fe_custom_set_card(
 custom_set_id int4  references fe_custom_set(custom_set_id) on delete
cascade,
 card_id int4 references fe_card(card_id),
 card_sequence int2,
 category text,
 primary key(custom_set_id, card_id)
);

and I want to implement this:

create table fe_custom_set_card(
 custom_set_id int4  references fe_custom_set(custom_set_id) on delete
cascade,
 card_id int4 references fe_card(card_id) on delete cascade,
 card_sequence int2,
 category text,
 primary key(custom_set_id, card_id)
);

How do I go about making this transition?  Dump the data, drop the
table, recreate it and load the data?

On Wed, 21 Mar 2001, Culley Harrelson wrote:

> Hi all,
>
> Im trying to find out the best way to implement cascading deletes on an
> existing table.  I have this:
>
> create table fe_custom_set_card(
>  custom_set_id int4  references fe_custom_set(custom_set_id) on delete
> cascade,
>  card_id int4 references fe_card(card_id),
>  card_sequence int2,
>  category text,
>  primary key(custom_set_id, card_id)
> );
>
> and I want to implement this:
>
> create table fe_custom_set_card(
>  custom_set_id int4  references fe_custom_set(custom_set_id) on delete
> cascade,
>  card_id int4 references fe_card(card_id) on delete cascade,
>  card_sequence int2,
>  category text,
>  primary key(custom_set_id, card_id)
> );
>
> How do I go about making this transition?  Dump the data, drop the
> table, recreate it and load the data?

It depends on how much work you're willing to do :)
I'd say, drop the three triggers associated with the second reference
(use drop trigger on them, you can find them by using the tgargs
and you need to quote the tgname when you use drop trigger) and
then use alter table to add the new constraint.