Update violates primary key

Question

My update receives the following error when updating to the database:

ORA-00001: unique constraint (DONALDBURY.BARS_ID_PK) violated

I am using the following code as my update. I am not wanting to get rid of the constraint but to find out a workaround for it. I want to stop the duplicates values being set.

MERGE INTO bars DEST_TABLE
USING (select :P3_filename as filename from dual) SOURCE_TABLE
ON (DEST_TABLE.name = SOURCE_TABLE.filename)
WHEN MATCHED THEN 
 UPDATE SET  image = ORDSYS.ORDImage()
WHEN NOT MATCHED THEN 
INSERT (
      image_id,
      filename,
      image,
      name,
      address,
      postcode,
      description)
      VALUES (:P3_image_id,
     :P3_filename,
      ORDSYS.ORDImage(),
     :P3_NAME,
     :P3_ADDRESS,
     :P3_POSTCODE,
     :P3_DESCRIPTION);

Can you add the structure of `DEST_TABLE` with available constraints — Pரதீப், Jan 02 '16 at 12:11
Please run this query: `select table_name, column_name, position from user_cons_columns where constraint_name = 'BARS_ID_PK';` and append a result of this query to the question. — krokodilko, Jan 02 '16 at 12:50
I'm confused. You have neither a list of columns (which is optional) nor the `VALUES` keyword. Does the above really work? — Gordon Linoff, Jan 02 '16 at 13:21

score 0 · Accepted Answer · answered Jan 02 '16 at 15:15

There is an unique constraint named BARS_ID_PK on image_id column - this constraint prohibits multiple rows from having the same value in this columns.

You are trying to insert a new row with image_id that already exists in the table.

To avoid this error, simply assing to :P3_image_id placeholder in the query a value, that doesn't exists yet in the table.

score 0 · Answer 2 · answered Jan 02 '16 at 15:42

Per the information provided, there is a primary key on the DEST table based on IMAGE_ID so duplicate IMAGE_IDs are not allowed. The MERGE statement checks for the existence of record based on filename (DEST_TABLE.filename). You would need to check on the image_id instead (or both filename and image_id). Based on the information provided, it seems that there may be multiple image_ids with the same file name in your bars table.

score -1 · Answer 3 · answered Jan 02 '16 at 19:36

-1

As a work around... I believe the Image ID is a numeric columns.. Find out the Max Image id from the table. Add MaxImageID+1 while inserting in to the table... This values will always be unique and hopefully resolve your issue.. Cheers..!!

answered Jan 02 '16 at 19:36

Indiecoder

186
3
17

Update violates primary key

3 Answers3