Insert with different key values to avoid contraint violation

insertoracleplsqltrace

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.

Best Answer

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.

Related Solutions

T-sql – Null Values in a CASE statement

Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause?

To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like:

DECLARE @i INT = 1 ;
DECLARE @rand INT ;
 WHILE @i <= 10
   BEGIN
    SET @rand = ABS(CHECKSUM(NEWID()))%10 +1 ;
    INSERT INTO TestEmployeeCountry 
    VALUES ( SUBSTRING('ABCDEFGHIJKLMNOP', @i, 1), 
        CASE @rand
            WHEN 1 THEN 'USA'
            WHEN 2 THEN 'CANADA'
            WHEN 3 THEN 'MEXICO'
            WHEN 4 THEN 'UK'
            WHEN 5 THEN 'FRANCE'
            WHEN 6 THEN 'SPAIN'
            WHEN 7 THEN 'RUSSIA'
            WHEN 8 THEN 'CHINA'
            WHEN 9 THEN 'JAPAN'
            WHEN 10 THEN 'INDIA'
        END) ;
    SET @i = @i + 1 ;
   END ;

Also notice that with your code, the 10 countries will not be placed in the table with equal probability! The first country (USA) will have 10% chance, the second will have 9% ( (100%-10%)*10% ), the third 8.1%, ((100%-19%)*10%), etc. That leaves a not so small chance (around 1/e) that none of the 10 is chosen and the CASE expression goes to the default ELSE NULL and you get the error. (You can check the probabilities if you allow nulls in the column and run the SQLfiddle script.)

According to the above, another way to solve it would be to change the expressions to comply with how SQL-Server executes the CASE and all 10 cases have the same probability:

    CASE 0
        WHEN ABS(CHECKSUM(NEWID()))%10 THEN 'USA'
        WHEN ABS(CHECKSUM(NEWID()))%9 THEN 'CANADA'
        WHEN ABS(CHECKSUM(NEWID()))%8 THEN 'MEXICO'
        WHEN ABS(CHECKSUM(NEWID()))%7 THEN 'UK'
        WHEN ABS(CHECKSUM(NEWID()))%6 THEN 'FRANCE'
        WHEN ABS(CHECKSUM(NEWID()))%5 THEN 'SPAIN'
        WHEN ABS(CHECKSUM(NEWID()))%4 THEN 'RUSSIA'
        WHEN ABS(CHECKSUM(NEWID()))%3 THEN 'CHINA'
        WHEN ABS(CHECKSUM(NEWID()))%2 THEN 'JAPAN'
        ELSE 'INDIA'
    END

PostgreSQL – How to Insert from Another Table and Remove Duplicates

Why not just use distinct on like this:

INSERT INTO entries(username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT DISTINCT ON (username, date, time, x0, x1, x2) username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;

But beware that this will retain the first row for every distinct combination -- which might not be the right thing without an order by clause this. But since you don't order in your question I presume you either don't care or haven't thought that far yet.

Best Answer

Related Solutions

T-sql – Null Values in a CASE statement

PostgreSQL – How to Insert from Another Table and Remove Duplicates

Related Question