Error: ORA 1
Text: unique constraint <table.column> violated
Cause: An update or insert statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
For Trusted Oracle7 configured in DBMS MAC mode, you may see this
message if a duplicate entry exists at a different level.
How do I resolve this? I do not see duplicate rows in my table and system does not allow me to drop the index.
I have access to database through Oracle Apex console
Thanks in advance!
if there are no duplicate rows in your db, what about the row you are trying to insert? does that row have the same id as any of the existing rows? if it's based on a sequence, what is the value of currval and nextval? does that exist already in the db? can you select a few nextvals off it to advance it so it doesn't overlap with the existing ids in your db?
You are trying to insert a duplicate value. Either disable your PK contraint or do not insert the value.
If you must insert the value and do not want to lose the contraint you can:
1. Disable/drop the constraint.
2. Insert values.
3. Create constraint disabled.
4. Enable contraint with no-validate. This will cause contraint to not validate past values, only new ones that get inserted.
One of the solution i found was to insert row manually by disabling the constraint. But it did not work after re-enabling the constraint. Also I do not want to leave the table w/o constraint as it is a central table.
visit_id is incremented on a sequence. Last number was 15770. I recreated the sequence with next value as 16000. but no luck
I do not want to insert duplicate value. My problem is that my create record does not happen from Apex form due to this error. Some how i want get rid of this and save record. Strange thing is that there are no duplicates in table, my sequence next value if far ahead that max visit_id. Still i am facing this!!
can dropping and recreating the table resolve this issue?
Compare the maximum value in the target table to the sequence value. If the sequence will return a value not in the target table and you get his error that implies your insert is not referencing the sequence generator in the insert statement and your code is somehow trying to insert an existing row. So your prolem is your APEX code.
HTH -- Mark D Powell --
ID the duplicates:
select id, count(1)
group by id
having count(1) > 1
If the PK won't create, you have a duplicate, the above query will find it.
Once you find it, remove all but 1 of them.
There is a well documented way to resolve this problem:
Let the docs be your friend! Keep this URL in mind whenever you encounter any Oracle related question: tahiti.oracle.com
APEX code was a problem!!! Had process which was doing untimely insert into visit_id. worked smoothly after i disabled that process.
Thanks for pointing that out.