user881703 user881703 - 4 months ago 9
SQL Question

Unique constraint error while insert record

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

Answer

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
Comments