Thread: cascade problems

for those who can help,

on my database i have two tables, organization and transaction, listed here;
-------------------------------------
create table organization(
organ_person_id          serial             primary key,
asset_id                       int,
fname                          text,
lname                          text,
phone                           varchar(15),
email                          varchar(20),
constraint organization_asset_id_fk foreign key(asset_id) references assets(asset_id));

create table transaction(
transaction_id                 serial           primary key,
trans_date                        timestamp   default  current_timestamp,
return_date                       timestamp,
organ_person_id               int,
constraint transaction_organ_person_id_fk foreign key(organ_person_id) references organization(organ_person_id) on delete cascade );

---------------------------------

what i want is for a delete on transaction to make a delete on organization where the organ_person_id s match up,with a query like;

delete from transaction where organ_person_id = 15;

 but this is not happening.  all it does is delete from transaction, and doesn't touch organization.  am i doing something wrong?  

please help,

mike z

-- 
Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14

It is the other way around - your transactin table is referencing organization. That means, that for every
organ_person_idvalue in 
transaction there must be a match in organization - if you "delete from organization where organ_person_id=15;" THEN
allthe rows 
from the transaction, having 15 in organ_person_id will be gone as well.That makes sense - if an organization no longer
exists,then 
all the transactions it used to own become invalid and get deleted.

And what you are doing, is just removing the transactions, you would need to have a constraint going the other way to
propagate
this delete to the organization (i.e. organization.organ_person_id should reference the transaction), but to do that,
youwould have 
to make organ_person_id in transaction unique...

Another way is to create an 'on delete' rule for the transaction table, to remove matching rows from organization as
well...
But are you sure this is really the behaviour you want (unlike the transaction->organization relationship, described in
the
beginning, this one does not make much sense to me)?

I hope, it helps...

Dima

Michael Zouroudis wrote:
> --------------020500090203030707070305
> Content-Type: text/plain; charset=us-ascii; format=flowed
> Content-Transfer-Encoding: 7bit
>
> for those who can help,
>
> on my database i have two tables, organization and transaction, listed here;
> -------------------------------------
> create table organization(
> organ_person_id          serial             primary key,
> asset_id                       int,
> fname                          text,
> lname                          text,
> phone                           varchar(15),
> email                          varchar(20),
> constraint organization_asset_id_fk foreign key(asset_id) references
> assets(asset_id));
>
> create table transaction(
> transaction_id                 serial           primary key,
> trans_date                        timestamp   default  current_timestamp,
> return_date                       timestamp,
> organ_person_id               int,
> constraint transaction_organ_person_id_fk foreign key(organ_person_id)
> references organization(organ_person_id) on delete cascade );
>
> ---------------------------------
>
> what i want is for a delete on transaction to make a delete on
> organization where the organ_person_id s match up,with a query like;
>
> *delete from transaction where organ_person_id = 15;*
>
>  but this is not happening.  all it does is delete from transaction, and
> doesn't touch organization.  am i doing something wrong?
>
> please help,
>
> mike z
>
>
>
>
>

On Thu, 19 Sep 2002, Michael Zouroudis wrote:

> for those who can help,
>
> on my database i have two tables, organization and transaction, listed here;
> -------------------------------------
> create table organization(
> organ_person_id          serial             primary key,
> asset_id                       int,
> fname                          text,
> lname                          text,
> phone                           varchar(15),
> email                          varchar(20),
> constraint organization_asset_id_fk foreign key(asset_id) references
> assets(asset_id));
>
> create table transaction(
> transaction_id                 serial           primary key,
> trans_date                        timestamp   default  current_timestamp,
> return_date                       timestamp,
> organ_person_id               int,
> constraint transaction_organ_person_id_fk foreign key(organ_person_id)
> references organization(organ_person_id) on delete cascade );
>
> ---------------------------------
>
> what i want is for a delete on transaction to make a delete on
> organization where the organ_person_id s match up,with a query like;
>
> *delete from transaction where organ_person_id = 15;*
>
>  but this is not happening.  all it does is delete from transaction, and
> doesn't touch organization.  am i doing something wrong?

The ON [DELETE|UPDATE] <action> clauses refer to the referenced table
not the referencing table.  The constraint says that if you delete
an organization the transactions associated with that organization
should be removed.