Disable all table constraints in Oracle

Question

How can I disable all table constrains in Oracle with a single command? This can be either for a single table, a list of tables, or for all tables.

Answer 1

It doesn't look like you can do this with a single command, but here's the closest thing to it that I could find.

Answer 2

It's not a single command, but here's how I do it. The following script has been designed to run in SQL*Plus. Note, I've purposely written this to only work within the current schema.

set heading off

spool drop_constraints.out

select
    'alter table ' || 
    owner || '.' || 
    table_name || 
    ' drop constraint ' || 
    constraint_name || ';'
from
    user_constraints;

spool off

set heading on

@drop_constraints.out

To restrict what you drop, filter add a where clause to the select statement:-

• filter on constraint_type to drop only particular types of constraints
• filter on table_name to do it only for one or a few tables.

To run on more than the current schema, modify the select statement to select from all_constraints rather than user_constraints.

Note - for some reason I can't get the underscore to NOT act like an italicization in the previous paragraph. If someone knows how to fix it, please feel free to edit this answer.

Answer 3

This can be scripted in PL/SQL pretty simply based on the DBA/ALL/USER_CONSTRAINTS system view, but various details make not as trivial as it sounds. You have to be careful about the order in which it is done and you also have to take account of the presence of unique indexes.

The order is important because you cannot drop a unique or primary key that is referenced by a foreign key, and there could be foreign keys on tables in other schemas that reference primary keys in your own, so unless you have ALTER ANY TABLE privilege then you cannot drop those PKs and UKs. Also you cannot switch a unique index to being a non-unique index so you have to drop it in order to drop the constraint (for this reason it's almost always better to implement unique constraints as a "real" constraint that is supported by a non-unique index).

Answer 4

It is better to avoid writing out temporary spool files. Use a PL/SQL block. You can run this from SQL*Plus or put this thing into a package or procedure. The join to USER_TABLES is there to avoid view constraints.

It's unlikely that you really want to disable all constraints (including NOT NULL, primary keys, etc). You should think about putting constraint_type in the WHERE clause.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

Enabling the constraints again is a bit tricker - you need to enable primary key constraints before you can reference them in a foreign key constraint. This can be done using an ORDER BY on constraint_type. 'P' = primary key, 'R' = foreign key.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
  END LOOP;
END;
/

Answer 5

In the "disable" script the order by clause should be that: ORDER BY c.constraint_type DESC, c.last_change DESC ... to disable the constraints in the right order.

Answer 6

Use following cursor to disable all constraint.. And alter query for enable constraints...

DECLARE

cursor r1 is select * from user_constraints; cursor r2 is select * from user_tables;

BEGIN FOR c1 IN r1 loop for c2 in r2 loop if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name); end if; end loop; END LOOP; END; /