Solved

oracle Disable the constraints of the tables

Posted on 2011-02-16
3
624 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.

Join & Write a Comment

Suggested Solutions

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now