Solved

oracle Disable the constraints of the tables

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

786 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