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.