加載資料時出現完整性約束錯誤

CREATE TABLE main_quest(
    e_id NUMBER(10) NOT NULL,
    CONSTRAINT pk_main_quest PRIMARY KEY ( e_id ));
    
insert into main_quest values(11);   
insert into main_quest values(12);
insert into main_quest values(13);
insert into main_quest values(14);
insert into main_quest values(15);
insert into main_quest values(16);
insert into main_quest values(17);
insert into main_quest values(18);

CREATE TABLE quest_staging (
    e_id            NUMBER(10),
    data_separator  VARCHAR2(100),
    CONSTRAINT pk_quest_staging PRIMARY KEY ( e_id )
);

insert into quest_staging values(11,'P');
insert into quest_staging values(12,'R');
insert into quest_staging values(13,'R P');
insert into quest_staging values(14,'C');
insert into quest_staging values(15,'C P');
insert into quest_staging values(20,'C P');

CREATE TABLE quest_ref (
    ref_id     NUMBER(10),
    ref_cat    VARCHAR2(50),
    ref_value  VARCHAR2(100),
    CONSTRAINT pk_quest_ref PRIMARY KEY ( ref_id )
);

insert into quest_ref values(1,'cat_1','PP');
insert into quest_ref values(2,'cat_1','R');
insert into quest_ref values(3,'cat_1','R P');
insert into quest_ref values(4,'cat_1','C');
insert into quest_ref values(5,'cat_1','C P');
insert into quest_ref values(6,'cat_1','I');
insert into quest_ref values(7,'cat_1','I P');
insert into quest_ref values(8,'cat_1','P');

CREATE SEQUENCE quest_main_sq;

CREATE TABLE quest_main (
    main_id number(10) DEFAULT quest_main_sq.NEXTVAL NOT NULL,
    e_id          NUMBER(10),
    ref_quest_id  NUMBER(10),
    CONSTRAINT pk_quest_main PRIMARY KEY ( main_id ),
    CONSTRAINT fk_quest_main FOREIGN KEY ( e_id )
        REFERENCES main_quest ( e_id )
);

我的嘗試：

MERGE INTO quest_main m
           USING (SELECT n.e_id,
                         n.data_separator,
                         qr.ref_id separator
                    FROM quest_staging n
                         JOIN quest_ref qr
                            ON     qr.ref_value = n.data_separator
                               AND qr.ref_cat = 'cat_1'
                                    ) x
              ON (m.e_id = x.e_id)
      WHEN MATCHED
      THEN
         UPDATE SET  m.ref_quest_id = x.separator
      WHEN NOT MATCHED
      THEN
         INSERT     (main_id,
                     e_id,
                     ref_quest_id
                     )
             VALUES (quest_main_sq.nextval,
                     x.e_id,
                     x.separator
                     );

面臨的問題：我想根據臨時表即quest_staging 和查找表即quest_ref 將記錄插入表主表即quest_main。如果臨時表中的 data_separator 列與查找表中的 ref_value 列匹配，則插入將發生到主表中。臨時表中的資料是 main_quest 表的子集。因此，如果在插入臨時表中的記錄時 main_quest 表中不存在 e_id，那么它應該跳過該記錄并插入剩余的記錄。但在這里我收到錯誤報告 - ORA-02291: 完整性約束 (TAM.FK_QUEST_MAIN) 違反 - 未找到父鍵，因為 e_id 20 不存在于 main_quest 表中。但我需要處理這個問題，它會跳過 e_id 20 并插入剩余的。

使用的工具：SQL Developer 版本：20.4.1.407.0006

預期輸出：

uj5u.com熱心網友回復：

如果我理解正確的話，它只是一個修復它的WHERE子句USING（參見第 7 - 10 行）：

SQL> MERGE INTO quest_main m
  2       USING (SELECT n.e_id, n.data_separator, qr.ref_id separator
  3                FROM quest_staging n
  4                     JOIN quest_ref qr
  5                        ON     qr.ref_value = n.data_separator
  6                           AND qr.ref_cat = 'cat_1'
  7               WHERE EXISTS
  8                        (SELECT NULL
  9                           FROM main_quest m
 10                          WHERE m.e_id = n.e_id)) x
 11          ON (m.e_id = x.e_id)
 12  WHEN MATCHED
 13  THEN
 14     UPDATE SET m.ref_quest_id = x.separator
 15  WHEN NOT MATCHED
 16  THEN
 17     INSERT     (main_id, e_id, ref_quest_id)
 18         VALUES (quest_main_sq.NEXTVAL, x.e_id, x.separator);

5 rows merged.

結果：

SQL> select * from quest_main;

   MAIN_ID       E_ID REF_QUEST_ID
---------- ---------- ------------
        24         12            2
        26         13            3
        28         14            4
        30         15            5
        32         11            8

SQL>

PS感謝您的測驗用例！

標籤：sql 甲骨文 plsql

上一篇：在ORACLE中使用來自同一個表的更新

下一篇：使用jsonb_to_recordset()函式時出現SQL錯誤