Drop Constraints in Oracle

The all_constraints view has all the constraints that your sign on has access to. A simple "query" will create the alter statements that you can use to drop the constraints.

Steps

  1. Make this kind of query:
    • SQL > select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||';' from all_constraints where table_name='GAMES';
      • alter table PRODUSER.GAMES drop constraint SYS_C006443;
      • alter table PRODUSER.GAMES drop constraint PK_GAMES;

Temporarily disable and re-enable constraints

  1. Maintain a table: You sometimes want to temporarily disable the constraints, then re-enable them. The following queries will create the SQL necessary to do this.
    • SQL > select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from all_constraints where table_name='GAMES';
      • alter table SITE.GAMES disable constraint SYS_C006443;
      • alter table SITE.GAMES disable constraint PK_GAMES;
    • SQL > select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' from all_constraints where table_name='GAMES';
      • alter table SITE.GAMES enable constraint SYS_C006443;
      • alter table SITE.GAMES enable constraint PK_GAMES;

Tips

  • Sometimes when you go to enable a constraint, it will not enable due to some violation of the constraint. You can sometimes bypass the violation and still enable the constraint by using the "novalidate" keyword like in the following example.
  • SQL > select 'alter table '||owner||'.'||table_name||' enable novalidate constraint '||constraint_name||';' from all_constraints where table_name='GAMES';
    • alter table SITE.GAMES enable novalidate constraint SYS_C006443;
    • alter table SITE.GAMES enable novalidate constraint PK_GAMES;

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Drop_Constraints_in_Oracle&oldid=317337"