In the test environment, to improve the deletion speed, the primary and foreign key constraints are sometimes disabled when data is deleted in batches, and the constraints are enabled after deletion.
But today, when the constraint is disabledORA-02297: Unable to disable Constraints
Cascade can be added for Cascade disabling constraints.
For example, alter table empt disable constraint empt_emp_no_pkCascade;
Remember to manually start the failed constraint after you use cascade to disable the constraint.
ALTER TABLE& Table_nameEnable Constraint& Constraint_name;
The following is a simulation:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report
SQL>
SQL> Create Table empt
2 (emp_no number (2) Constraint empt_emp_no_pk primary key,
3 ename varchar2 (15 ),
4 salary number (8, 2 ),
5 mgr_no number (2) Constraint empt_mgr_fk references empt );
Table created
SQL> select
2 constraint_name,
3 constraint_type,
4 r_constraint_name,
5 delete_rule,
6 Status
7 from user_constraints
8 where table_name = 'empt ';
Constraint_name constraint_type r_constraint_name delete_rule status
----------------------------------------------------------------------------------------------
Empt_emp_no_pk P Enabled
Empt_mgr_fk R empt_emp_no_pk no action enabled
SQL> ALTER TABLE empt disable constraint empt_emp_no_pk;
ORA-02297: Unable to disable constraints (report. empt_emp_no_pk)-correlated
SQL> ALTER TABLE empt disable constraint empt_emp_no_pkCascade;
Table altered
SQL> select
2 constraint_name,
3 constraint_type,
4 r_constraint_name,
5 delete_rule,
6 Status
7 from user_constraints
8 where table_name = 'empt ';
Constraint_name constraint_type r_constraint_name delete_rule status
----------------------------------------------------------------------------------------------
Empt_emp_no_pk P disabled
Empt_mgr_fk R empt_emp_no_pk no action disabled
SQL> ALTER TABLE empt enable constraint empt_emp_no_pk;
Table altered
SQL>
SQL> select
2 constraint_name,
3 constraint_type,
4 r_constraint_name,
5 delete_rule,
6 Status
7 from user_constraints
8 where table_name = 'empt ';
Constraint_name constraint_type r_constraint_name delete_rule status
----------------------------------------------------------------------------------------------
Empt_emp_no_pk P Enabled
Empt_mgr_fk R empt_emp_no_pk no action disabled
SQL> ALTER TABLE enable constraint empt_emp_no_pk;
Alter table enable constraint empt_emp_no_pk
ORA-01735: Invalid alter table Option
SQL> ALTER TABLE empt enable constraint empt_mgr_fk;
Table altered
SQL>