Unique constraint error while insert record

0
votes

I got an error while inserting record in to the MY_TABLE as

ORA-00001: unique constraint (TEST.MY_TABLE_PK) violated

That might be due to the duplicate TRANS_ID. But I don't have any idea to sort-out this issue. How can I avoid ORA-00001 error and insert record in to the MY_TABLE. 

  CREATE UNIQUE INDEX "MY_TABLE_PK" ON "MY_TABLE_TRANS" ("TRANS_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TEST" ;
2
sqloracle
What problem are you trying to solve? Insert records and then delete duplicate keys or this keys become duplicate after commit? – Evgeniy K.
i need to insert record in to the MY_TABLE, in that case i got error as ORA-00001: unique constraint (TEST.MY_TABLE_PK) violated, i gess that error due to the MY_TABLE_PK – user881703
You can avoid this by not inserting the same value for TRANS_ID twice – a_horse_with_no_name
@a_horse_with_no_name yes, could you please let me know how can debug/edit my MY_TABLE_PK – user881703
You don't need to "edit" your table. You need to change your INSERT statement so that they don't insert the same value twice. – a_horse_with_no_name

2 Answers

1
votes

Either drop the index, if you don't care about duplicate

ALTER TABLE MY_TABLE_TRANS DROP INDEX MY_TABLE_PK;

Or select only unique records with a group by \ distinct :

SELECT t.trans_id , MAX(Other Column) , MAX(...
FROM MY_TABLE_TRANS t
GROUP BY t.trans_id
0
votes

Since your TRANS_ID already exist into your MY_TABLE_TRANS table, you can also UPDATE the current record (if you have the rights for doing it of course)

Validate what is the record first.

SELECT *
FROM MY_TABLE_TRANS
WHERE trans_id = 'your trans_id here'

Take a look at the record. If you want to modify it, you might use an UPDATE instead of a new INSERT.

UPDATE MY_TABLE_TRANS
SET (put your field that need to be updated) = 'your value'
WHERE trans_id = 'your trans_id here'