Solved

oracle Disable the constraints of the tables

Posted on 2011-02-16
3
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

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 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

688 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