1

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys i'm trying to insert.

INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");

Error:

*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

My question: Is there a way to avoid this problem and tell the database to insert another key value if it already exists? It's important also to keep the values of other columns..

I have searched already for some possible solutions and the best approach would be to use the PL/SQL Exception to handle that. I just can't see how to do that until now.

DECLARE
BEGIN
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39822, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39823, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39824, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
INSERT INTO "COMPONENTS" ( "ID_COMPONENT", "CODE", "DESCRIPTION", "MODEL", "RESP" ) VALUES
(39825, "101087632", "COMPONENT TEST", "TEST", "ADMIN");
exception
   when DUP_VAL_ON_INDEX then
   --do the insert. but how?
END;

Any suggestions? Thanks in advance.

  • Which columns form the primary key? Is it the id_component? – ypercubeᵀᴹ Jun 5 '14 at 17:04
  • Yes, id_component. – Luke Skywalker Jun 5 '14 at 17:04
  • If you are willing to allow some ID_COMPONENT values to change, why not just change them all to some unused range? – RLF Jun 5 '14 at 17:50
  • But how? I've thought about replacing the key values with ID_COMPONENT.NEXTVAL, but since I have a huge amount of INSERT queries, this task will take long if I replace them one by one. – Luke Skywalker Jun 5 '14 at 18:17
  • What if you run the insert statements in some type of temp / staging table, change the id_components in the set and insert that into the final table? – SQLChao Jun 5 '14 at 20:23
1

I'd think it's probably not a best practice to use EXCEPTION to do logic/handling, such as duplicate keys. Here's an idea: write a stored proc. Also create a sequence that has your alternate values that will be used in cases of duplicate keys. You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data). Then do your inserts via this stored proc whenever you want the dupe-sub functionality.

CREATE TABLE components (
    id_component INTEGER PRIMARY KEY
  , code         VARCHAR2(255) 
  , description  VARCHAR2(255)
  , model        VARCHAR2(255)
  , resp         VARCHAR2(255)
);

CREATE SEQUENCE components_alt_id_seq
  START WITH 1000000;

CREATE OR REPLACE PROCEDURE component_ins
  (  p_id_component IN components.id_component%TYPE
   , p_code         IN components.code%TYPE 
   , p_description  IN components.description%TYPE 
   , p_model        IN components.model%TYPE 
   , p_resp         IN components.resp%TYPE )
AS
  v_is_duplicate INTEGER;
BEGIN
  SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
  IF v_duplicate = 0 THEN
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (p_id_component, p_code, p_description, p_model, p_resp);
  ELSE
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
  END IF;
END;
/

EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')

And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.

SELECT id_component FROM components;

ID_COMPONENT
------------
       39822
       39823
       39824
       40015
       40016
     1000005
     1000006

7 rows selected.    
  • That's a very nice approach. But what if the table index reaches 1,000,000? Would be better to get the last key value and increment instead, right? Also, COMPONENTS is not the only table that receives these INSERTS. Anyway, I'll try to fit it to my need and give a feedback later. Thanks a lot! – Luke Skywalker Jun 6 '14 at 13:05
  • 1
    You could set the sequence to start as high as you need. If 1,000,000 is not high enough, then how about 100,000,000,000? Ugly though. I thought about the "last key + 1" approach, but that runs into transactional problems: suppose you have two sessions connected to the DB and they both try to insert at the same time, they will both get the same new key value. Both inserts happen without errors, the first one to commit will get no error, but the 2nd commit will fail. The most sane approach might be to generate the key from the sequence on all inserts. – Joshua Huber Jun 6 '14 at 14:04
  • I got your point, but I'd run it without anyone connected to the DB. Now I'm working around with your solution to reach a most "dynamic" point in order to cover the inserts on the other tables. Thank you very much, Joshua. – Luke Skywalker Jun 6 '14 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.