# Insert with different key values to avoid contraint violation

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;


• 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

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

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
• 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