Re: cascade problems

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.