Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

How to catch uniqueness attributes?

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.
Posts: 1276
IBM DB2 Netbeans IDE Spring Java
  • 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).

Posts: 3181
Android Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • 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).

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

Posts: 2407
Scala Python Oracle Postgres Database Linux
  • 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.
Paper beats rock. Scissors beats tiny ad.
the value of filler advertising in 2020
    Bookmark Topic Watch Topic
  • New Topic