Handling duplicate values

[Erikxxx]

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

 

[Thargy]

Erikxxx,

I don't think there's a single "right" answer here, so I'm giving my three hap'orth of input, ok?

You obviously have appropriate unique constraints on the tables - good. Why not just have the web page insert the data and let it handle a constraint error if it pops up?

That way you just write an insert statement and an error handler on the page, nothing else. Since the amount of data is tiny, network traffic and the round trip delay to the database and back should be negligible.

I think you should KISS (keep it simple stupid) here.

I know others will disagree, but these are my reasons/thoughts on the matter. I hope it helps.

Regards

Tharg

Grinding away at things Oracular

 

[Erikxxx]

Hi Thargtheslayer,

Yeah you are probably quite right for the example I mentioned in the previous post. I'm also curious to know principles/ best practices applied by others in large-scale development projects and on larger data sets than I described. In the small scale projects I have taken part in I would say we been quite bad at using exception handling on the database level. Instead our application layer(java) is dealing with this. In addition I find it a bit annoying and time consuming to write all these small logical checks to see if the value I want to insert already exist in the database etc. But as you proposed I could just try to insert the data and make sure I capture the exceptions that can be thrown.

Anyway, with new projects coming up in the nearest future I'm quite interested in knowing more about how other people more experienced than me are dealing with these kind of issues when designing their systems.

Thanks
Erik

 

[SantaMufasa]

The First Principle of Well-Behaved Data is "Let bad data live as short a life as possible."

The Second Priciple is "Let the database (with declarative constraints) enforce as much integrity as possible."

So, my recommendations agree with the above: "Use declarative constraints to enforce integrity, rejecting the bad data at the earliest point after input."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Erikxxx]

Hi Mufasa,

Thanks for your feedback. Does this mean you suggest that the database entirely should handle possible exceptional behaviours instead of going down the route of writing test select queries prior to inserts in order to find out if I'm trying to insert duplicate values. I'm tempted to leave this extra iteration of select statements out and entirely rely on exception handlers in my procedures.

Thanks
Erik

 

[SantaMufasa]

Yes, that is what I recommend since your "test select" queries will access index blocks, then read data blocks, then when your do the INSERT, Oracle will still need to access the unique index to confirm that the INSERT will not disobey the UNIQUE constraint.

Whereas, if you allow Oracle to just "do its thing", your INSERT will check the unique index for unique compliance, and if it is "good", your INSERT is successful; if it is "bad", your get your error...much better performance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Erikxxx]

Thanks for your help Mufasa!!