Solved

oracle Disable the constraints of the tables

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

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

Independent Software Vendors: 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

732 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