unique constraint (%s.%s) violated

Question

I have a problem with my procedure below. I have records for different item numbers (order numbers) that have been purchased a number of times. When I return part of the purchase based on the item number, it should delete the record from the items table and insert the item details into a table called `deleted_items'.The problem is when I delete part of the order once at a time, the table deleted_Items will come with this error:

ORA-06512: at line 4 00001. 00000 - "unique constraint (%s.%s) violated" *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.

  create or replace PROCEDURE RET_Item(Item_no number(5))
           AS
       CURSOR Return_i
          IS
       SELECT * FROM Items
       WHERE item_num = Item_no
       AND Item_num IN (SELECT Item_num FROM deleted_items);
     cur1 Return_i%ROWTYPE;
   BEGIN    
   OPEN Return_i;
    LOOP
     FETCH Return_i INTO cur1;
    EXIT WHEN Return_i%NOTFOUND;
     IF cur1.Delivered IS NOT NULL THEN

     Insert into deleted_itmes values(
         cur1.Order_No,cur1.Item_num,cur1.Delivered,User,sysdate,   
           'Item_returned');

     DELETE FROM Items WHERE Item_num = Item_no ;
     DELETE FROM Purchase where Item_num = Item_no;
    DBMS_OUTPUT.PUT_LINE('Item number ' || Item_no ||' deleted! ');
     ELSIF cur1.Delivered IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Cannot return item: '|| cur1.Item_num);
   END IF;
  END LOOP;
 CLOSE Return_i;
END RET_item;

Items Table

         Order_No      Item_num      Delivered   
        ----------    ----------     --------- 
           1001          1234        12-JUN-15           
           1002          5678        01-JAN-15  
           1003          9087        05-FEB-15

Purchase Table

             Order_No     Item_num     Quantity  
            ----------   ----------   --------------- 
               1001        1234           10
               1001        9087           7
               1002        9087           10 
               1003        1234           1 
               1003        5678           12

Deleted_Item Table

Items Table

      Order_No   Item_num    Delivered   Name_on-order  Date_of_Ret  Status

vmachan vmachan · Accepted Answer · 2016-01-03T02:31:27

It is unclear from your code, what table you are INSERTing into.. could you paste the whole INSERT statement?

If you are inserting into the DELETED_ITEMS table then why do you need the

AND Item_num IN (SELECT Item_num FROM deleted_items)

Shouldn't it be

AND Item_num NOT IN (SELECT Item_num FROM deleted_items)

unique constraint (%s.%s) violated

1 Answers