Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: delete child table rows based on parent table predicates

Re: delete child table rows based on parent table predicates

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sat, 10 Jan 2004 20:53:43 GMT
Message-ID: <pan.2004.01.10.20.55.07.44298@adelphia.net>


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)
Received on Sat Jan 10 2004 - 14:53:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US