CODEWITHSUNDEEP
sqloracle-database
user881703
user881703

Reputation: 1199

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

Upvotes: 0

Views: 2734

Answers (2)

Marc Giroux
Marc Giroux

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

sagi
sagi

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

Related Questions