Home » RDBMS Server » Server Administration » Constraints
Constraints [message #242865] Tue, 05 June 2007 05:58 Go to next message
sussudio
Messages: 30
Registered: March 2005
Location: London
Member
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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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?

Regards
Michel

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

DECLARE
  stmt    varchar2(1000);
  c_rec   all_constraints%ROWTYPE;

BEGIN

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


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).

JR

[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: Sat Dec 10 20:28:56 CST 2016

Total time taken to generate the page: 0.28116 seconds