Getting integrity constraint error while loading the data-CodePudding

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 )
);

My Attempt :

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
                     );

Problem facing: I want to insert records into the table main table i.e quest_main based on the staging table i.e quest_staging and lookup table i.e quest_ref. If the data_separator column in the staging table matches with the ref_value column in the lookup table then inserting will happen into the main table. And data present in the staging table is a subset of the main_quest table. So, if e_id is not present in the main_quest table while inserting the records from the staging table then it should skip that record and insert the remaining. But here I am getting errors like Error report - ORA-02291: integrity constraint (TAM.FK_QUEST_MAIN) violated - parent key not found because e_id 20 is not present in the main_quest table. But I need to handle this that it will skip e_id 20 and insert the remaining.

Tool used: SQL Developer Version: 20.4.1.407.0006

Expected output :

CodePudding user response：

If I understood you correctly, it is just a WHERE clause in USING that fixes it (see lines #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.

Result:

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>

P.S. Thank you for test case!