测试环境中为了提高删除速度,批量删除数据的时候有时会先禁用主外键约束,在删除后在启用约束。
但今天在禁用约束的时候出现了 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