How to delete a record in a one to many situation when using a list in form

[rich]

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?

[Mladen Gogala]

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

[ImOk]

Easiest way is to delete and then insert. But you should do it one
record at a time. You dont want to delete all at once and then insert.
Otherwise if you have a system crash right after the delete you could
lose massive amounts of data instead of just one record.

To do this you need to add a new character type 'delete' flag column in
your items table which would be used for this purpose.

1) Update all the records you want to delete by putting in X in the
deleteFlag.
..
2) Delete the first one whose key matches the one you are inserting
from the list.

3) Insert the new one from the list (leave the deleteFlag column null
or put a blank)

Repeat the 1-3 process until you've inserted all your records from the
list.

4) Now delete all the records that still have an X in the delete flag
column. These would be the left over records.

You can also use Transactions but its only safe as long as your server
will not crash during commit.