Home » SQL & PL/SQL » SQL & PL/SQL » Handling duplicate values
Handling duplicate values [message #220076] Sun, 18 February 2007 12:14 Go to next message
swedstar
Messages: 7
Registered: November 2006
Junior Member
Hi,

I need suggestions in how to deal with the following scenario.

Currently I've got a user registration form (html) with the following fields:username, firstname, lastname and email address.

Usernames and email addresses are unique within the system and my database tables are checking for uniqueness. Up until now I have been running
two different queries prior to processing the insert statement in order to check if username and/or email address already exist in the database. Now I'm wondering if it's possible (or better) to remove
the need of running these two queries and instead let my pl/sql procedure throw an exception and deal with it that way. I added an exception block
to the userregistration procedure which is now catching a 'unique constraint' exception if duplicates are inserted.

Does this seem to be a better approach in how to handle duplicates? or is it better to do the checking of duplicates before I process the insert statement? I'm planning to implement the same structure to other parts of the system where I need to check for duplicate values.

Thanks
Erik
Re: Handling duplicate values [message #220078 is a reply to message #220076] Sun, 18 February 2007 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Does this seem to be a better approach in how to handle duplicates?
The method of catching the ORA-00001 error is mostly more efficient, but since the old way is now working; I would not change it.
Re: Handling duplicate values [message #220083 is a reply to message #220078] Sun, 18 February 2007 13:46 Go to previous messageGo to next message
swedstar
Messages: 7
Registered: November 2006
Junior Member
What is generally a better approach to do if you designing from scratch? Also,
in the example I described where I'm planning to use Exceptions. How would I be able to distinguish between which unique constraint is thrown? In my example, a unique constraint exception could be thrown for a 'username' and/or for an 'email'. My front-end application need to know which one is thrown in order to display appropriate error message.

Thanks
Erik
Re: Handling duplicate values [message #220103 is a reply to message #220083] Sun, 18 February 2007 20:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For the exact reason you've specified, it's generally better not to rely on the exception.

If the SELECT finds a row, then you're no worse off - you've still had to run a SQL. If the SELECT does not find a row, then at least you've cached the index blocks you'll need when you do the INSERT.

This technique - although marginally slower - is very unlikely to kill an OLTP system. It is - however - difficult to scale for bulk processing. MERGE and 10g EXCEPTIONS INTO are better methods for high volume DML.

This one is not black-and-white. It's a trade off between robustness and performance. Whichever way you go, you probably won't kick yourself afterwards for making the wrong decision.

Ross Leishman
Re: Handling duplicate values [message #220240 is a reply to message #220103] Mon, 19 February 2007 15:48 Go to previous message
swedstar
Messages: 7
Registered: November 2006
Junior Member
Hi rleishman,

Thanks for your feedback. I've been developing small Oracle applications for a while now which are quite easy to maintain since we haven't got alot of code or huge data volumes. I guess these design decisions are even more important
in large-scale database environments. It's always interesting to know how other people dealing with similar problems and there is always alot to learn from others.

Cheers
Erik
Previous Topic: insead of trigger
Next Topic: JOINS
Goto Forum:
  


Current Time: Sun Dec 04 22:49:02 CST 2016

Total time taken to generate the page: 0.04265 seconds