Thread: cascading
to: anyone who can help:
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 searched all day and seemed to be doing what the docs told me to, but to no avail. it's been bothering me all night so if anybody knows how to work the cascade effect properly please email me back at either of these addresses;
mazuru@prodigy.net
mazuru@aol.com
mzouroudis@idealcorp.com
thanks in advance for the help,
mike
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 searched all day and seemed to be doing what the docs told me to, but to no avail. it's been bothering me all night so if anybody knows how to work the cascade effect properly please email me back at either of these addresses;
mazuru@prodigy.net
mazuru@aol.com
mzouroudis@idealcorp.com
thanks in advance for the help,
mike
--
Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14
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.