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;


数据运维技术 » ORA-02266: unique/primary keys in table referenced by enabled foreign keys ORACLE 报错 故障修复 远程处理