时间戳

首页 > 解决方案 > 如果没有一个完整的键保留表，则无法从视图中删除

oracle11g - 如果没有一个完整的键保留表，则无法从视图中删除

问题描述

目前，我想从不同的购物车表中删除该用户的商品。所以我想出了它。

DELETE FROM (SELECT *
FROM EXH_CART EC, PRODUCT_CART PC, MEMBER M
WHERE PC.USER_NUM(+) = M.USER_NUM
AND EC.USER_NUM(+) = M.USER_NUM
AND M.USER_NUM = 6);

但是这段代码给了我这个错误。

SQL error: ORA-01752: cannot delete from view without exactly one key-preserved table
01752. 00000 - "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.

表结构

我该如何解决这个错误？请帮忙。

标签： oracle11g

解决方案

Per the documentation of the DELETE command (https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8005.htm#SQLRF01505), the syntax does not support multiple tables, joins, or other similar constructs. You must issue separate delete commands for each related table:

DELETE FROM MEMBER M WHERE M.USER_NUM = 6;
DELETE FROM EXH_CART EC WHERE EC.USER_NUM = 6;
DELETE FROM PRODUCT_CART PC WHERE PC.USER_NUM = 6;
COMMIT;

As I mentioned in the comments, ideally you would have a formal foreign key relationship between the child and parent tables, with the ON DELETE CASCADE option set so that you would then only have to delete the record from the parent table and let the database handle the child records automatically.

推荐阅读