Posted by Mladen Gogala on 12/18/97 11:48

rich wrote:
> I am building a database and I am using a list where I can make
> multiple choices. The data is like this
>
> Master table
> item1id
> item2
> index(item1id)
>
> detail table
> item1id
> item2id
> index (item1id, item2id)
>
> item2 table
> item2id
> item2
> index(item2id)
>
> My form would have
>
> Item1
>
> list of all the choices for item2 table where I can make multiple
> choices.
>
> This piece is easy enough to do inserts with. Loop on the listbox
> array and insert.
> Now the question is what do I do to:
> 1. Make changes
> 2. Delete an item.
>
> Do I instead of update, build a function where I delete all the items
> in the detail table belonging to item1 and then insert the new list?
> Or is there a more elegant way to handle this?
>

Nope, you should just define the foreign key with "ON DELETE CASCADE"
option. You should do something like:

ALTER TABLE DETAIL ADD CONSTRAINT FK_MASTER_DETAIL
FOREIGN KEY(ITEM1) REFERENCES MASTER(ITEM1) ON DELETE CASCADE;

This is how it works on the known EMP and DEPT tables:

SQL> alter table emp add constraint fk_dept_emp
2 foreign key(deptno) references dept(deptno) on delete cascade;

Table altered.

SQL>

--
Mladen Gogala
http://www.mgogala.com

• Next in forum: file_get_contents on Server 2003 IIS 6.0
• Prev in forum: Fetching and managing Mime Mail
• Thread view: Re: How to delete a record in a one to many situation when using a list in form

[Reply to this message]