File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Deleting from multiple Tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Deleting from multiple Tables" Watch "Deleting from multiple Tables" New topic

Deleting from multiple Tables

H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Hi there

I've got 2 tables in my Database... These two tables are linked via one-to-one relationship... Now i want to delete record thats in both tables, how do i do this? if i try to delete it from one it will tell me it cant be deleted because its being used by another table!

how do i go about this?
I'm using MS Access

[ February 12, 2006: Message edited by: H Melua ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
I don't know anything about Access, but in general there are a few different approaches.

In many databases, when there is a parent-child relationship between two tables, enforced by a Foreign Key constraint, the constraint can be specified with something like "ON DELETE CASCADE"; then when a record in the parent table is deleted, the database will automatically delete any dependent records in any child tables.

For most database (and maybe even Access) and most constraints, you should be able to delete rows in the reverse order in which they were inserted. For example, if you insert a parent row and then a child row that has a Foreign Key reference on the parent, then you need to delete all the child rows that refer to a parent before you can delete the parent.

Another approach when you've defined a circular reference (e.g. "chicken" refers to "egg" and vice-versa) is to take advantage of transactionality and constraint deferability (if your database supports it, I don't think Access does though). First you have to enable deferability on your constraints. Then you would do something like:
set autocommit off
delete the chicken row
delete the egg row
(There's really no order to the deletes, the other order works too).
(In general with JDBC and a database that supports transactions, you should always set autocommit off, no matter what you're doing.)
H Melua
Ranch Hand

Joined: Jan 04, 2005
Posts: 172
Oh man, you're a real rescuer, Thank you soooooooo much, the delete on cascade did the trick

Thanks again, i've been stressing like mad over this , i spent several days pulling my hair and banging my head on the wall trying to find a solution!

I'm glad Access can do one thing useful!

I agree. Here's the link:
subject: Deleting from multiple Tables
It's not a secret anymore!