ORA-01752: cannot delete from view without exactly one key-preserved table ORACLE 报错 故障修复 远程处理

文档解释

ORA-01752: cannot delete from view without exactly one key-preserved table

Cause: The deleted table had
– no key-preserved tables,
– more than one key-preserved table, or
– the key-preserved table was an unmerged view.

Action: Redefine the view or delete it from the underlying base tables.

ORA-01752错误表示尝试在视图上删除数据时发生了错误。

官方解释

ORA-01752：不能在没有一个键保持表的情况下删除视图。

删除操作必须有至少一个键保持表。这意味着视图的SELECT子句中必须是单一表，其中所有主键列均出现在SELECT语句中，而另外的表列可以出现。

常见案例

例如，视图定义为由两个表组成的简单联接，而这两个表之间没有任何连接：

CREATE OR REPLACE VIEW v_test AS

SELECT col1, col2

FROM table1, table2;

尝试在这个视图上删除数据时，将会收到ORA-01752错误：

DELETE FROM v_test

WHERE col1 = ‘xxx’

正常处理方法及步骤

如果你想在视图上删除数据，请确保视图定义为具有单一表以及至少一个主键列的联接，例如：

CREATE OR REPLACE VIEW v_test AS

SELECT col1, col2

FROM table1

WHERE col1 IN (SELECT col1 FROM table2);

这样，可以删除视图表中的数据：

DELETE FROM v_test

WHERE col1 = ‘xxx’