Search code examples
sqloracle-databaseplsqlsql-merge

Update violates primary key

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);
Solution

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