Home » RDBMS Server » Server Administration » Constraints
Constraints [message #242865] Tue, 05 June 2007 05:58 Go to next message
Messages: 30
Registered: March 2005
Location: London
I need a quick way to drop all the contraints on a set of tables so I can import free from errors. Is there a quick way or will I just have to go through each table ans disable the contraints.

Many thanks for any help that you give.
Re: Constraints [message #242866 is a reply to message #242865] Tue, 05 June 2007 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
so I can import free from errors

free from errors?
If I understand you want to disable the constraints to ignore the errors.
But the errors are still there.

Import disables the constraints at the beginning (but PK and UNIQUE ones) and tries to reenable it at the end.
Why do you want to workaround this process?


Re: Constraints [message #242986 is a reply to message #242866] Tue, 05 June 2007 12:01 Go to previous message
Messages: 35
Registered: February 2006

  stmt    varchar2(1000);
  c_rec   all_constraints%ROWTYPE;


  /* Remove foreign keys */
  FOR c_rec IN
    (select *
     from all_constraints
     where owner='xxx'
     and table_name like 'yyy%'
     and constraint_type = 'R')
    stmt :=  ' ALTER TABLE ' || c_rec.owner ||
             '.' || c_rec.table_name ||
             ' DROP CONSTRAINT ' || c_rec.constraint_name;
    execute immediate stmt;

This gets rid of the foreign keys. Modify the owner and table_name predicates to select specific tables. Change the contraint_type predicate to select other constraint types (C = check, U = unique, P = primary key).


[Updated on: Tue, 05 June 2007 12:01]

Report message to a moderator

Previous Topic: Generating STATISTICS
Next Topic: System account keeps locking
Goto Forum:

Current Time: Mon Oct 24 18:45:27 CDT 2016

Total time taken to generate the page: 0.05409 seconds