aspose file tools*
The moose likes JDBC and the fly likes Delete from multiple tables. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Delete from multiple tables. " Watch "Delete from multiple tables. " New topic
Author

Delete from multiple tables.

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Problem is this: I have to delete from TableA,B,C. Table A's PK is an FK in Table B and Table C.

I can do this as three DELETE statements.
Delete from table b;
delete from table c;

and finally, delete from table A.

Is this a good approach. Is there a better approach to achieve this ?

Thanks
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Not so much better as alternate. Many DBs support cascade deletion through foreign key constraints. To do this, you have to create or modify the constraint to do cascade deletion. For example, here's the MySQL syntax:
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Then, when the row in the primary table is deleted, the rows in the other 2 tables are checked and any necessary deletes are performed for you by the database.

Personally, I like this style; it reduces network round-trips to the DB and simplifies the Java code. However, it means that your "logic" is split between your Java application and your database application, and some Java developers have issues with that.
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Thanks Stu,

I was also reading a similar article regarding Oracle. http://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php
But havent tried or used it yet. When it says, alter the table I get a bit concerned here. have you heard of any issues/performance problems with cascade delete ?

Atul
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
For the time being I am planning to do a batch update here using three delete statements.

I assume there wont be any issues here. If anyone sees any please let me know.

Thanks
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Atul Mishra:
For the time being I am planning to do a batch update here using three delete statements.

I assume there wont be any issues here. If anyone sees any please let me know.

Thanks


That should be fine. Assuming you're using PreparedStatments correctly, cascade delete might be just the tiniest bit faster on Oracle than using batch, but the difference isn't so much that I would worry about it at all.

By the way, I'm assuming that child column in your foreign key constraint is indexed; if not, your performance will stink as your tables get big, no matter how you do the deletes.

For example:
Table A - primary key on column named ID
Table B - foreign key constraint between B.PARENT_ID and A.ID

Every time a row in A is deleted (or has ID updated), the constraint on B is checked. If there's not an index on PARENT_ID, then the entire table B must be scanned which is relatively slow on big tables; if there is an index, the check is very quick.
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Stu derby,

I am using prepared statements.

Thanks for the note about indexing. My tables doesnt have any indexes.
I have to start reading about it and implemnting it.

Thanks for the point.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Delete from multiple tables.