Home » SQL & PL/SQL » SQL & PL/SQL » How to delete data without disable constraints (oracle 10g db)
How to delete data without disable constraints [message #577343] Thu, 14 February 2013 05:34 Go to next message
vikasg2006
Messages: 70
Registered: March 2006
Location: Dubai
Member

I want to delete data from say 100 tables without disable the constraints.
Tables having foreign key.. which is giving error if I select table randomly to delete.
I want to know the query which will give me the sequence of all tables based upon dependencies, so that If i delete the data on that sequence, it wont give me child record exists error.


Re: How to delete data without disable constraints [message #577346 is a reply to message #577343] Thu, 14 February 2013 05:48 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
why you don't want to disable foreign key.

you can not delete a record which have any child record.


thanks...........

[Updated on: Thu, 14 February 2013 05:49]

Report message to a moderator

Re: How to delete data without disable constraints [message #577347 is a reply to message #577343] Thu, 14 February 2013 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's your database, you should know which tables are related to which other tables.
If you're not sure query user_dependencies.
Or just recreate the all the foreign keys as on delete cascade and just delete the top level parent tables.
Re: How to delete data without disable constraints [message #577348 is a reply to message #577346] Thu, 14 February 2013 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
neetesh87 wrote on Thu, 14 February 2013 11:48
you can not delete child records.

Since when?
Re: How to delete data without disable constraints [message #577353 is a reply to message #577343] Thu, 14 February 2013 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Query all_constraints.

But what if you have a loop in the constraints?
How will make it without disabling them?

Regards
Michel
Re: How to delete data without disable constraints [message #577467 is a reply to message #577343] Sat, 16 February 2013 07:42 Go to previous messageGo to next message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hi,

You shoud try this, it will display table names in the correct order :

select
table_name from
(select
table_name,max(level) maxlevel
from
(select t1.table_name table_name, t2.table_name pk_table_name from
user_constraints t1,
user_constraints t2,
user_tables t3,
user_tables t4
where
t1.constraint_type='R' and
t1.status='ENABLED'
t1.r_constraint_name=t2.constraint_name and
t1.table_name=t3.table_name and t2.table_name=t4.table_name)
connect by prior table_name=pk_table_name
group by table_name
union
(select table_name, 0 maxlevel from user_tables minus select table_name,0
from user_constraints where constraint_type='R' and status='ENABLED')
)
order by maxlevel desc
;

However as Michel pointed out if you have a loop in your foreign keys you will get an error message :

ORA-01436: CONNECT BY loop in user data

If this happens you should disable constraints involved in loops (one constraint per looop) and try again.

How this will help.

Regards

Emmanuel
Re: How to delete data without disable constraints [message #577473 is a reply to message #577467] Sat, 16 February 2013 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@TuneMyQuery

Welcome to the forum and thank you to answer to questions.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Responding take care: don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:

Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Note that in your query, with a quick look, t3 and t4 are useless (I didn't check the rest).

Regards
Michel
Re: How to delete data without disable constraints [message #577474 is a reply to message #577473] Sat, 16 February 2013 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 22500
Registered: January 2009
Senior Member
SQL> ED
Wrote file afiedt.buf

  1  select
  2  table_name from
  3  (select
  4  table_name,max(level) maxlevel
  5  from
  6  (select t1.table_name table_name, t2.table_name pk_table_name from
  7  user_constraints t1,
  8  user_constraints t2,
  9  user_tables t3,
 10  user_tables t4
 11  where
 12  t1.constraint_type='R' and
 13  t1.status='ENABLED'
 14  t1.r_constraint_name=t2.constraint_name and
 15  t1.table_name=t3.table_name and t2.table_name=t4.table_name)
 16  connect by prior table_name=pk_table_name
 17  group by table_name
 18  union
 19  (select table_name, 0 maxlevel from user_tables minus select table_name,0
 20  from user_constraints where constraint_type='R' and status='ENABLED')
 21  )
 22* order by maxlevel desc
 23  /
t1.r_constraint_name=t2.constraint_name and
*
ERROR at line 14:
ORA-00907: missing right parenthesis


SQL> 




Oracle reports error with posted SQL.

Do not post SQL with syntax errors.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How to delete data without disable constraints [message #577476 is a reply to message #577474] Sat, 16 February 2013 10:21 Go to previous message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Sorry for the mistake, this one works :
SELECT table_name 
FROM   (SELECT table_name, 
               Max(LEVEL) maxlevel 
        FROM   (SELECT t1.table_name table_name, 
                       t2.table_name pk_table_name 
                FROM   user_constraints t1, 
                       user_constraints t2, 
                       user_tables t3, 
                       user_tables t4 
                WHERE  t1.constraint_type = 'R' 
                       AND t1.status = 'ENABLED' 
                       AND t1.r_constraint_name = t2.constraint_name 
                       AND t1.table_name = t3.table_name 
                       AND t2.table_name = t4.table_name) 
        CONNECT BY PRIOR table_name = pk_table_name 
        GROUP  BY table_name 
        UNION 
        (SELECT table_name, 
                0 maxlevel 
         FROM   user_tables 
         MINUS 
         SELECT table_name, 
                0 
         FROM   user_constraints 
         WHERE  constraint_type = 'R' 
                AND status = 'ENABLED')) 
ORDER  BY maxlevel DESC; 

* BlackSwan formatted &added {code} tags. Do so yourself in the future.

[Updated on: Sat, 16 February 2013 10:56] by Moderator

Report message to a moderator

Previous Topic: Using Minus operator to get the changed data info between two tables
Next Topic: Updating a table using another table
Goto Forum:
  


Current Time: Fri Jul 25 16:24:10 CDT 2014

Total time taken to generate the page: 0.22232 seconds