ORA-02297: Unable to disable Constraints

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>