ORA-02266: unique/primary keys in table referenced by enabled foreign keys ORACLE 报错 故障修复 远程处理
文档解释
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.
Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;
。
ORA-02266:表中引用的外键已启用时,该表中未定义唯一/主键等约束。
官方解释
ORA-02266指出表中引用的外键已启用,但是表中没有定义唯一或主键约束。这可能是由于表中的一列或多列没有定义为唯一的,或者没有任何列组合被定义为索引以构成主键,而且不允许重复值。
常见案例
例如,CREATE TABLE T1下定义一个表:
CREATE TABLE T1 (col1 NUMBER, col2 NUMBER, col3 NUMBER);
这个表未定义任何索引,也没有定义主键约束,而当我们在另一个关联表T2中提供col2引用作为外键时,它会抛出此错误消息。
正常处理方法及步骤
1.为表T1定义唯一索引或主键约束,使其不允许重复值。
处理方法是增加一个UNIQUE或主键的约束,我们可以添加如下:
CREATE UNIQUE INDEX t1_idx ON T1 (col1, col2, col3);
或此约束:
ALTER TABLE T1 ADD CONSTRAINT t1_cp CONSITRAINT PRIMARY KEY (col1, col2, col3);
2.禁用由表T1引用的外键约束,以使ORA-02266错误不再发生。
处理方法是禁用外键约束:
ALTER TABLE T2 DISABLE CONSTRAINT FK_COL1;