This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to catch uniqueness attributes?

 
Tom Nguyen
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my database, I set username and email are unique. So how can I check if there is a username or password or both violate that uniqueness requirement?

This is my insertion code:



I understand that if there is any violation, the rowCount will not execute and it will go to the catch block. But the error is too general to catch. I want to catch more specific violation in detail such as violated username or violated email or both so that I can inform the user which one he should change.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQLException object exposes some methods you can use to retrieve SQL State after executing a statement against your DBMS. Unluckily, as far as I know there isn't a standard set of SQL codes (they should be vendor specific).




 
Ron McLeod
Bartender
Pie
Posts: 1047
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tom Nguyen wrote:.. how can I check if there is a username or password or both violate that uniqueness requirement? ..

You could check to see if either the name or email values exist before trying to insert the values in to a record, then only proceed with inserting if they did not already exist. If you were paranoid about multiple threads trying to insert the same name and/or email values to the table at the same time, you could also lock the table before checking and inserting, and unlock immeadiately after (if this capability exists with your particular database).

LOCK TABLES users WRITE
SELECT COUNT(*) FROM users WHERE name=? OR email=?
INSERT INTO users (name, email, password) VALUES (?, ? ,?)
UNLOCK TABLES


 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Put a unique key constraint on the relevant columns in your database table. This will create a unique index over those columns which is maintained and checked by the database. This means that duplicates cannot be created, no matter how the user tries to submit the data to the database, because the database will stop them with an exception, and it's transaction-safe. It also means the uniqueness check can be optimised via the index, as it is much quicker to check a unique index for a given value than read the entire table to check for duplicates. You can have multiple unique constraints on a table if you have different columns that must each be unique.

Even if you want to run a "SELECT COUNT..." to look for duplicates, as Ron suggests, it will run much quicker if the search columns are indexed, and if you're putting an index on there, you might as well make it a unique index if the columns are supposed to be unique anyway. Incidentally, "SELECT 1 FROM users WHERE name=? OR email=?" will return as soon as it finds a single record with the given details i.e. you don't need to read the entire table if you find a duplicate in the first record.

However, I would advise against trying to do this by counting records and locking tables, because it's hard to get this stuff right and you will almost certainly miss some conditions which allow people to write duplicates, or where nobody can write anything because somebody has managed to leave the tables locked while they go off on holiday. Locking tables will also create a serious bottleneck in a multi-user system. Use the excellent tools that the database provides to ensure uniqueness, instead of trying to re-invent the wheel.

So that's how you ensure uniqueness on your chosen columns.

Of course, you will need to recognise that your application has raised an exception after trying to insert a duplicate record. The specific exception will depend on your database, but this should be fairly easy to find out with a bit of research. It's up to you what you do in response to this exception.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic