Re: cascade problems

It is the other way around - your transactin table is referencing organization. That means, that for every organ_person_id value in
transaction there must be a match in organization - if you "delete from organization where organ_person_id=15;" THEN all the 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, you would 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
>
>
>
>
>