Solved

oracle Disable the constraints of the tables

Posted on 2011-02-16
3
628 Views
Last Modified: 2012-05-11
Good morning, I'm doing a data migration script, my question is:
 is a good choice?
 1. Disable the constraints of the tables
 2. Inserting data
 3. Enabling constraints for tables
 If yes, please indicate the code to disable the contraints of 8 tables
 Thanks
0
Comment
Question by:enrique_aeo
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 34908316
It depends.  It there is a LOT of data and you think keeping the constraints in place causes to much overhead
or
you cannot migrate the data because of temporary violations and cannot defer them, then disable them.

Otherwise, there shouldn't be any problem keeping them in place.

Check out the following link.  It provides a SQL script to generate the SQL necessary:
http://www.databasedesign-resource.com/disabling-constraints.html



0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
ID: 34908576
why you are disabling the constraints? they are created to maintain data integrity... if you disable them and insert data then probably you will not be enable them again... even if you do, you will have issues later...

instead create a backup table with the same structure of original table but without any constraint, say table_temp
then insert your data here and work on this data until they can be inserted into original table without disabling the constraints... this way you will have valid data in the original table...
0
 
LVL 1

Expert Comment

by:tweaver2
ID: 34909001
If you know that your data is sound you should be able to insert into the target tables in an order that will not violate your constraints.  

If you just want to hack stuff in, I would not recommend it for production data, but I have done it for testing purposes.

The following will drop constraints tied to a table:

declare v_command varchar2(500);
begin
      for fk in (select child_owner, child_table_name, constraint_name as fk_name
                 from adm.vw_constraints
                 where (parent_owner = '<OWNER>' and parent_table_name = '<TABLE_NAME>' and status = 'ENABLED') or
                        child_owner = '<OWNER>' and child_table_name = '<TABLE_NAME>' and status = 'ENABLED') loop

         v_command := 'alter table ' || fk.child_owner||'.'||fk.child_table_name || ' disable constraint ' || fk.fk_name;
         
         execute immediate v_command;

      end loop;
end;
/
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question