Oracle Disable Constraints & Enable Constraints

Mehmet Salih Deveci

3 years ago

I will explain how to Disable Constraints & Enable Constraints in Oracle in this post.

Oracle Disable Constraints & Enable Constraints

Sometimes you may need to enable or disable the Oracle Constraints.

Disable Constraint in Oracle

You can disable the existing constraint as follows. If you couldn’t disable the constraint, you can use the Cascade option as follows.

SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME;
Table altered.



SQL> alter table TABLE_NAME disable constraint CONSTRAINT_NAME cascade;
Table altered.

Enable Constraint in Oracle

You can enable the existing constraint as follows.

SQL> alter table TABLE_NAME enable constraint CONSTRAINT_NAME;
Table altered.

If you want to disable all constraints, use the following script.

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

To enable all constraints, use the following script.

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

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/