2019-01-10 · Develop

ORA-02266: 表中的唯一/主键被启用的外键引用

场景

今天在 TRUNCATE TABLE PMSUSER.QRTZ_TRIGGERS 的时候出现如下的错误情况(使用 DataGrip 数据库工具操作 Oracle 11g

[42000][2266] ORA-02266: 表中的唯一/主键被启用的外键引用

分析

如果存在父子表关系,即一张表的主键是其他的表的外键关联着的,在对这个表进行 TRUNCATE TABLE 操作就会出现上面的情况。

解决方案

Step 1 找出相关外键信息

SELECT fk.CONSTRAINT_NAME, fk.TABLE_NAME
FROM ALL_CONSTRAINTS fk
LEFT JOIN ALL_CONSTRAINTS pk ON fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME AND fk.R_OWNER = pk.OWNER
WHERE fk.CONSTRAINT_TYPE = 'R' AND fk.OWNER = 'PMSUSER' AND pk.TABLE_NAME = 'QRTZ_TRIGGERS'

将返回大致如下的信息

oracle-constraint-pk-fk

Step 2 使外键约束失效

ALTER TABLE PMSUSER.QRTZ_BLOB_TRIGGERS DISABLE CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK
ALTER TABLE PMSUSER.QRTZ_SIMPROP_TRIGGERS DISABLE CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK
ALTER TABLE PMSUSER.QRTZ_CRON_TRIGGERS DISABLE CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK
ALTER TABLE PMSUSER.QRTZ_SIMPLE_TRIGGERS DISABLE CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK

Step 3 再次执行清表操作

TRUNCATE TABLE QRTZ_TRIGGERS PRESERVE MATERIALIZED VIEW LOG REUSE STORAGE

Step 4 恢复外键约束

ALTER TABLE PMSUSER.QRTZ_BLOB_TRIGGERS ENABLE CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK;
ALTER TABLE PMSUSER.QRTZ_SIMPROP_TRIGGERS ENABLE CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK;
ALTER TABLE PMSUSER.QRTZ_CRON_TRIGGERS ENABLE CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK;
ALTER TABLE PMSUSER.QRTZ_SIMPLE_TRIGGERS ENABLE CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK;