Reputation: 1199
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" ;
Upvotes: 0
Views: 2734
Reputation: 192
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'
Upvotes: 0
Reputation: 40491
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
Upvotes: 1