Re: delete on cascade

On Mon, 23 Oct 2006, Luca Ferrari wrote:

> Hi all,
> I guess this is an already asked question, but I didn't found an answer, so
> apologize me. Imagine I've got two tables:
> skill(id,description) // primary key => id
> family(id,description) // primary key => id
> and I want to associate skills to families:
> ass_sf(id_skill,id_family) // appropriate foreign keys

Using the type information from the original and assuming it's the same
for family, without referential actions that'd look something like:

Create table skill(id varchar(20) primary key, description varchar(50));
Create table family(id varchar(20) primary key, description varchar(50));
Create table ass_sf(id_skill varchar(20) references skill,
id_family varchar(20) referenced family);

> Tables are already created and the database is running. Now I'd like to
> implement a delete cascade, thus when I delete a skill also its association
> with the family must be deleted.

In this case, you'd need to remove the constraint on ass_sf.id_skill and
replace it with one like foreign key(id_skill) references skill on delete
cascade which you can do with alter table in two steps. The on delete
information is associated with the foreign key and deletes matching rows
from the referencing table (ass_sf) when rows in the referenced tablen
(skill) are deleted.

You may also want to think about what the on update behavior should be.