The moose likes Oracle/OAS and the fly likes deleting from tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "deleting from tables" Watch "deleting from tables" New topic
Author

deleting from tables

deepak carter
Ranch Hand

Joined: Feb 19, 2011
Posts: 165
Hi All,

I am working on a requirement.

There is one table.I want to delete a row from this table but i cannot delete it because

1.This table is referred by 79 table through foreign key relationship
2.Each of those 79 tables are referred by other 100's of table

so you can imagine its kind of tree structure.So i need to start from bottom and reach to TOP.I need to do it through SQL

So from where I should start ???Do i need to create a temp table or anything which i can do any approach which can be useful???

Note:CASCADE DELETE NOT ALLOWED
deepak carter
Ranch Hand

Joined: Feb 19, 2011
Posts: 165
I AM USING ORACLE 10g
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

This is a pretty strange requirement.

Does anyone actually know all the consequences? Aren't there some really important data among the dependent tables at some level? It is certainly possible - and not that complicated - to do this, but if someone didn't do an analysis of the schema, you might be in for a nasty ride. And if someone did an analysis - how come the table structure isn't part of that analysis?

Make sure there is a working backup before going any further.

A few pointers:

If the tables are all stored in a single schema, you could get the list of foreign keys from the USER_CONSTRAINTS view. Foreign constraints listed in this view contain the name of the parent key in the R_CONSTRAINT_NAME column. You'll then "translate" the constrain name to the table - again using USER_CONSTRAINTS. You might create a view that maps child tables to parent tables directly, and then use hierarchical query to obtain all dependent tables. Then delete them in reverse order. Make sure to use only ENABLED constraints, otherwise you might delete things you don't want to.

If the tables are not in one schema, you'd need an account having access to all the tables and use ALL_CONSTRAINTS instead of USER_CONSTRAINTS.

If there is a lot of data to delete, it can take a really, really long time. Your undo tablespace/rollback segments might even overflow. Assuming you want to erase all data from all affected tables, it might be better to use TRUNCATE. A parent table cannot be truncated while it has enabled foreign keys, so you'd have to disable them all and enable them after the truncate. It's a bit more work programming, but the time savings can be huge. Plus, the allocated space will be freed from the table (just deleting rows leaves unused space allocated in the tables; normally that doesn't matter, but in this case it might be desirable to free the space up).

Some tables might contain foreign keys allowing null values. Should records in these tables that contain null FK values be deleted or left there? If they should be left there, that would complicate the things a bit (or perhaps a lot), and of course, you won't be able to use TRUNCATE.
deepak carter
Ranch Hand

Joined: Feb 19, 2011
Posts: 165
i dont have access to user_constraints and might not get one
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

In a standard setup, everyone has access to the USER_CONSTRAINTS and ALL_CONSTRAINTS views. You don't have access to DBA_CONSTRAINTS, which is fine - you don't need that.

The right to select from these views is granted to PUBLIC. A DBA would have to revoke that grant to prevent you from seeing the views, which would break many internal tools of Oracle itself, so I'd be really surprised if someone did it somewhere. I might be mistaken, though, so if you really cannot query USER_CONSTRAINTS, your task probably cannot be done. You can try SYS.USER_CONSTRAINTS instead, if the inaccessibility of that view is caused by some synonym.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: deleting from tables