Unique constraint error while insert record

Question

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" ;

What problem are you trying to solve? Insert records and then delete duplicate keys or this keys become duplicate after commit? — Evgeniy K.
– Evgeniy K., Commented Jul 13, 2016 at 8:00
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
– user881703, Commented Jul 13, 2016 at 8:02
You can avoid this by not inserting the same value for TRANS_ID twice — user330315
– user330315, Commented Jul 13, 2016 at 8:10
@a_horse_with_no_name yes, could you please let me know how can debug/edit my MY_TABLE_PK — user881703
– user881703, Commented Jul 13, 2016 at 8:14
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. — user330315
– user330315, Commented Jul 13, 2016 at 8:15

sagi · Accepted Answer · 2016-07-13 08:01:57Z

1

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

answered Jul 13, 2016 at 8:01

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user881703 Over a year ago

Thanks for your feedback, is that possible to edit my MY_TABLE_PK statement without dropping

sagi Over a year ago

Edit it into what ?

Marc Giroux · Accepted Answer · 2016-07-13 12:35:07Z

0

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'

answered Jul 13, 2016 at 12:35

Marc Giroux

1921 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Unique constraint error while insert record

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related