Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements.

My question is in addition to oracle check constraints do I have to check error situations myself or let oracle do the job?

For example if I have a column in a table which is unique and I want to insert into this table I have to check the value to be unique or let the oracle unique constraint do the job and produce an error if it is a repeated value?

share|improve this question
add comment

marked as duplicate by Max Vernon, Jack Douglas Apr 15 '13 at 5:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

In my experience it's slightly faster to let oracle check it and throw an excpetion if the constraint is violated. If you want to do something special when that happens, you can catch the exception and handle it.

For instance:

EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         UPDATE ...
share|improve this answer
    
can i detect which field of the table causes the error in the exception handling block? –  rahim asgari Apr 14 '13 at 11:47
1  
Not sure about that. I used to check for constraint or index names in sqlerrm. With those, if you still need, you can query the Data Dictionary for the name of the column(s) they were created for. –  Pearl Apr 14 '13 at 17:41
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.