oracle ORA-02292：刷新实体化视图时的完整性约束

x33g5p2x 于 2023-05-16 发布在 Oracle

关注(0)|答案(1)|浏览(184)

Oracle生产版本19.17.0.0.0
我有一个表，其中包含一个实体化视图的外键，该表是从父表填充的。
在父表上删除并重新插入相同的记录（这不会在实体化视图中造成任何差异）会导致外键中断。

CREATE TABLE TEMP_20230509_PARENT
(
    ID INT PRIMARY KEY
);
INSERT INTO TEMP_20230509_PARENT (ID)
VALUES (1);
CREATE MATERIALIZED VIEW LOG ON TEMP_20230509_PARENT WITH ROWID, PRIMARY KEY;

-- MV pointing to the Parent
CREATE MATERIALIZED VIEW TEMP_20230509_PARENT_MV AS
SELECT *
FROM TEMP_20230509_PARENT;

-- child table that points to the MV
CREATE TABLE TEMP_20230509_CHILD
(
    ID        INT PRIMARY KEY,
    PARENT_ID INT,
    CONSTRAINT TO_PARENT_FK
        FOREIGN KEY (PARENT_ID)
            REFERENCES TEMP_20230509_PARENT_MV (ID)
);

-- child record pointing to the MV
INSERT INTO TEMP_20230509_CHILD
VALUES (1, 1);

-- insert new record in parent, should not affect the child
INSERT INTO TEMP_20230509_PARENT (ID)
VALUES (2);
-- 🟩 full refresh works
BEGIN
    DBMS_MVIEW.REFRESH('TEMP_20230509_PARENT_MV');
END;

-- delete and reinsert the same record on the parent table
DELETE
FROM TEMP_20230509_PARENT A
WHERE A.ID = 1;
INSERT INTO TEMP_20230509_PARENT (ID)
VALUES (1);
COMMIT;

-- 🟥 full refresh fails
-- ORA-02292: integrity constraint (IMPACT_NG.TO_PARENT_FK) violated - child record found
BEGIN
    DBMS_MVIEW.REFRESH('TEMP_20230509_PARENT_MV');
END;

oracle

来源：https://stackoverflow.com/questions/76207574/ora-02292-integrity-constraint-when-refreshing-materialized-view

1条答案

按热度按时间

blmhpbnm1#

对我来说，看起来好像MV刷新首先 * 删除 * 行（这就是引发错误的原因，因为它确实删除了父键），然后 * 插入 * 它们。
这里有一个解决方案，看看是否有帮助：创建外键为INITIALLY DEFERRED DEFERRABLE。
父表：

SQL> CREATE TABLE TEMP_20230509_PARENT
  2  (
  3      ID INT PRIMARY KEY
  4  );

Table created.

SQL> INSERT INTO TEMP_20230509_PARENT (ID)
  2  VALUES (1);

1 row created.

MV日志：

SQL> CREATE MATERIALIZED VIEW LOG ON TEMP_20230509_PARENT WITH ROWID, PRIMARY KEY;

Materialized view log created.

MV本身：

SQL> -- MV pointing to the Parent
SQL> CREATE MATERIALIZED VIEW TEMP_20230509_PARENT_MV AS
  2  SELECT *
  3  FROM TEMP_20230509_PARENT;

Materialized view created.

子表-注意第8行：

SQL> -- child table that points to the MV
SQL> CREATE TABLE TEMP_20230509_CHILD
  2  (
  3      ID        INT PRIMARY KEY,
  4      PARENT_ID INT,
  5      CONSTRAINT TO_PARENT_FK
  6          FOREIGN KEY (PARENT_ID)
  7              REFERENCES TEMP_20230509_PARENT_MV (ID)
  8              INITIALLY DEFERRED DEFERRABLE              --> here
  9  );

Table created.

SQL> -- child record pointing to the MV
SQL> INSERT INTO TEMP_20230509_CHILD
  2  VALUES (1, 1);

1 row created.

新建父行：

SQL> -- insert new record in parent, should not affect the child
SQL> INSERT INTO TEMP_20230509_PARENT (ID)
  2  VALUES (2);

1 row created.

刷新：

SQL> -- full refresh works
SQL> BEGIN
  2      DBMS_MVIEW.REFRESH('TEMP_20230509_PARENT_MV');
  3  END;
  4  /

PL/SQL procedure successfully completed.

删除/插入到父项中：

SQL> -- delete and reinsert the same record on the parent table
SQL> DELETE
  2  FROM TEMP_20230509_PARENT A
  3  WHERE A.ID = 1;

1 row deleted.

SQL> INSERT INTO TEMP_20230509_PARENT (ID)
  2  VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

这是以前失败的，但现在有效：

SQL> -- full refresh WORKS
SQL> BEGIN
  2      DBMS_MVIEW.REFRESH('TEMP_20230509_PARENT_MV');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

就是这样

赞(0）回复(0）举报 2023-05-16

我来回答

oracle ORA-02292：刷新实体化视图时的完整性约束

1条答案

相关问题

热门标签

最新问答