Re: cascading

On Fri, 13 Sep 2002, Michael Zouroudis wrote:

> i am developing a db in pl/pgsql at work and i am having problems with
> one final part; the deleting of ref. int. rows. there are three
> tables, which i will list below as close as possible to what i remember
> (i'm at home and don't have my notes).
>
> create table goodbye (
> goodbye_id serial primary key,
> whenn text,
> wheree text,
> how text,
> isbn int
> );
>
> create table hello (
> hello_id serial primary key,
> goodbye_id int,
> how text,
> isbn int,
> constraints hello_goodbye_id_fk foreign key references
> goodbye(goodbye_id) on delete cascade
> );
>
> create table aidos (
> adios_id serial primary key,
> cost money,
> title text
> );
>
> create table goodbye_adios (
> goodbye_id int,
> adios_id int,
> constraint goodbye_adios_goodbye_id_fk foreign key references
> goodbye(goodbye_id) on delete cascade,
> constraint goodbye_adios_adios_id_fk foreign key references
> adios(adios_id) on delete cascade
> );
>
> like i said, this is as close as i can remember.
>
> the problem that occurs is that when i do a delete statement on the
> goodbye_adios table, it will automatically delete from the goodbye_adios
> table and the goodbye table, but not the record from the adios table. i

I assume you mean a delete from goodbye will delete from hello and
goodbye_adios since that's what appears to be correct from the above.

You have no constraints that would delete from adios listed, the
on delete cascade in goodbye_adios means that a delete on adios will
delete a row in goodbye_adios, but it doesn't do the inverse.