Re: delete child table rows based on parent table predicates

On Fri, 09 Jan 2004 09:35:50 -0500, jim wrote:

> I have two tables that are related by keys. For instance,
>
> Table employee {
> last_name char(40) not null,
> first_name char(40) not null,
> department_name char(40) not null,
> age int not null,
> ...
> }
> Employee table has a primary key (combination of last_name and first_name).
>
> Table address {
> last_name char(40) not null,
> first_name char(40) not null,
> street char(200) not null,
> city char(100) not null,
> ...
> }
> Address table has a primary key (combination of last_name, first_name and
> street in which (last_name, first_name) reference (last_name, first_name) in
> employee table.
>
> Now I want to delete some rows in Address table based on department_name in
> Employee table. What is sql for this delete?
>
> I appreciate your help. Please ignore table design and I just use it for my
> problem illustration.
>
>
> Jim

ALTER TABLE ADDRESS ADD CONSTRAINT ADDRESS_EMP_FK FOREIGN KEY (last_name,first_name)
REFERENCES employee(first_name,last_name) ON DELETE CASCADE;

-- 
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)