Unique constraint (SchemaName.DATA1_PK)

Question

I am inserting data into a table by selecting from CURSOR but I am getting a unique constraint violation. Here is the query:

 CURSOR cRestParmRestData(nSetId in number) IS
    SELECT distinct rstdata.rest_id,
           rstdata.rest_inst_id,
           rstdata.pren_set_id,
           rstdata.cusm_set_id,
           rstdata.asn_set_typ,
           rstdata.mkt_id,
           rstdata.crte_ts,
           rstdata.crte_user_id
      FROM  stg_rfm2_rest_data rstdata,
            stg_rfm2_rest rest,
            stg_rfm2_sets srs,
            stg_rfm2_dset srd
     WHERE (
              rest.rest_id = rstdata.rest_id
              AND rest.rest_inst_id = rstdata.rest_inst_id
              AND rstdata.stg_restaurant_id = rest.stg_restaurant_id
            )
       and srs.set_id = rstdata.cusm_set_id
       and srd.set_id = srs.set_id
       and rstdata.cusm_set_id = nSetId
       and srs.typ = nCustomParmSetTyp;





       IF (rcRestSets.sets_typ IN (nCustomParmSetTyp) ) THEN
        BEGIN
          vBlockDescription := 'INSERT INTO rest_data for setId:'||rcRestSets.sets_set_id;
        for rcRstData in cRestParmRestData(nSetId => rcRestSets.sets_set_id) loop
          INSERT INTO rest_data
           (rest_id,
           rest_inst_id,
           pren_set_id,
           cusm_set_id,
           asn_set_typ,
           mkt_id,
           crte_ts,
           crte_user_id)

          VALUES
          (rcRstData.rest_id,
           rcRstData.rest_inst_id,
           rcRstData.pren_set_id,
           rcRstData.cusm_set_id,
           rcRstData.asn_set_typ,
           rcRstData.mkt_id,
           sysdate,
           rcRstData.crte_user_id);
        END LOOP;
       EXCEPTION
        WHEN OTHERS THEN
        nTemp := pkg_misc.fn_insertLogDetail(nLogId, 'Parameter Set', vBlockDescription, null, null,TO_CHAR(SQLCODE)||' , '||'Error '||dbms_utility.format_error_backtrace, SUBSTR(SQLERRM,1,500)||'sets_crtr_node_id =>'||rcRestSets.sets_crtr_node_id );
            vLogStus :=1;
       END;

      end if;

I am not sure what I am doing wrong, I am getting exception

"ORA-00001: unique constraint (EU3MARKETS.REST_DATA_PK) violatedsets_crtr_node_id =>326" -at Error ORA-06512: at EU3MARKETS.PKG_LOADRFM2RESTAURANTDB, line 983 (INSERT INTO REST_DATA Line)

score 0 · Answer 1 · answered Apr 02 '15 at 07:10

0

You are inserting one of the ID value which is a primary key, Does not allow duplicate values. Check the primary key value in Data Base and insert a unique id.

answered Apr 02 '15 at 07:10

Vikram

215
2
13

score 0 · Answer 2 · answered Apr 02 '15 at 08:44

0

The issue appears to be the fact that you're selecting data (including your pk columns) from your rest_data table, and then trying to reinsert them back into the same table. Since you haven't changed the values in the pk columns, you're essentially trying to create duplicate rows, which the constraint prohibits.

What exactly are you trying to do and why?

Also, why are you taking a procedural/row-by-row approach, when you could be doing a set-based insert into ... select ... approach? That will be much faster and easier to maintain!

answered Apr 02 '15 at 08:44

Boneist

22,910
1
25
40

I am collecting data from tables 'stg_rfm2_rest' , 'stg_rfm2_rest_data ' , etc. into a CURSOR and using this CURSOR to insert data in another table ''rest_data " – Jatin Gupta Apr 02 '15 at 09:17
Ah right, sorry; I misread the `stg_rfm2_` as `stg_rfm2.` Duh! In which case, you're still selecting rows that have values in the pk columns which already exist in the rest_data table. You need to either skip those rows or find another way of making them unique. – Boneist Apr 02 '15 at 09:29

Unique constraint (SchemaName.DATA1_PK)

2 Answers2