cascading deletes through a 1-M join table

Hi all,
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David