Disable/enable external keys and triggers in Oracle

1. The foreign key constraint names in the Oracle database can be found in the user_constraints table. Constraint_type = 'R' indicates a foreign key constraint.

2. Run alter table table_name enable constraint constraint_name to enable the foreign key constraint.

3. The command to disable foreign key constraints is: alter table table_name disable constraint constraint_name

4. Then, use SQL to find out the constraint names of keys other than the database:

Select 'alter table' | table_name | 'Enable constraint' | constraint_name | ';' from user_constraints where constraint_type = 'R'

Select 'alter table' | table_name | 'Disable constraint' | constraint_name | ';' from user_constraints where constraint_type = 'R'

Disable/enable external keys and triggers in Oracle

In a lot of database maintenance work, we often encounter import and export operations on existing data, but there are many external relationships between tables in the database, data cannot be operated directly according to maintenance requirements. All foreign keys and triggers need to be temporarily disabled, and then corresponding foreign keys and triggers must be enabled after data operations and operations are completed, the following script disables and enables the foreign key and trigger of the entire user object:

-- Disable scripts
Set serveroutput on size 100000
Begin
For C in (select 'alter table' | table_name | 'Disable constraint' | constraint_name | ''as v_ SQL from user_constraints where constraint_type = 'R') loop
Dbms_output.put_line (C. V _ SQL );
Begin
Execute immediate C. v_ SQL;
Exception when others then
Dbms_output.put_line (sqlerrm );
End;
End loop;
For C in (select 'alter table' | tname | 'Disable all trigger' as v_ SQL from tab where tabtype = 'table') loop
Dbms_output.put_line (C. v_ SQL );
Begin
Execute immediate C. v_ SQL;
Exception when others then
Dbms_output.put_line (sqlerrm );
End;
End loop;
End;
/

-- Enable script
Set serveroutput on size 100000
Begin
For C in (select 'alter table' | table_name | 'Enable constraint' | constraint_name | ''as v_ SQL from user_constraints where constraint_type = 'R') loop
Dbms_output.put_line (C. V _ SQL );
Begin
Execute immediate C. v_ SQL;
Exception when others then
Dbms_output.put_line (sqlerrm );
End;
End loop;
For C in (select 'alter table' | tname | 'Enable all triggers' as v_ SQL from tab where tabtype = 'table') loop
Dbms_output.put_line (C. v_ SQL );
Begin
Execute immediate C. v_ SQL;
Exception when others then
Dbms_output.put_line (sqlerrm );
End;
End loop;
End;
/