I have suddenly started getting this error on one of my table ssr_visit
I have visit_id as primary key bind by sequence. I have tried following but no use...
1. Drop constraint and insert record manually.
2. tried making index non-unique but could not succeed as it gave error
PLS HELP!! I am bit new to database. Answer with steps in layman's tern would be appreciated!!
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.
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 --
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