ORA-02297: 无法禁用约束条件

测试环境中为了提高删除速度，批量删除数据的时候有时会先禁用主外键约束，在删除后在启用约束。

 

但今天在禁用约束的时候出现了 ORA-02297: 无法禁用约束条件

可以加cascade进行级联禁用约束

如：ALTER TABLE empt DISABLE CONSTRAINT empt_emp_no_pk CASCADE;

在使用cascade禁用约束后要记得手工启动失效的约束

alter table  &table_name enable constraint  &constraint_name;

 

 

下面模拟一下：

 

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: 无法禁用约束条件 (REPORT.EMPT_EMP_NO_PK) - 存在相关性

 

 

SQL> ALTER TABLE empt DISABLE CONSTRAINT empt_emp_no_pk CASCADE;

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: 无效的 ALTER TABLE 选项

 

SQL> alter table EMPT enable constraint EMPT_MGR_FK;

Table altered

SQL>