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 rows with referential integrity 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 rows with referential integrity" Watch "deleting rows with referential integrity" New topic

deleting rows with referential integrity

Judy Collins

Joined: Oct 07, 2004
Posts: 20
Hi there,
I need to delete specific records from database (e.g. those whose expirydate column has date greater than that of today). At least 10 other tables have reference to this master table and then each of the 10 tables are referenced by other tables. ( i guess you understand what i m trying to say :roll: )

Is there any option available except figuring out all of the tables and then deleting the records?

I am sure there must be some better way.

Any help in this regard is greatly appreciaed
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

I will recommend to have one additional column as enable/disable flag, and whenever you dont need record simple disable them(Soft delete)

otherwise you can delete them by cascading constraints, I do this in oracle somtimes but NOT recommended.

what about child record ? dont you want to delete them ?

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Judy Collins

Joined: Oct 07, 2004
Posts: 20
Shailesh Chandra
Thaks for your response. I modified schema and added constraint "on delete cascade" and it appears to be fine. Can you please tell me why don't you recommend cascade?

I agree. Here's the link:
subject: deleting rows with referential integrity
It's not a secret anymore!