How to catch uniqueness attributes?

[Tom Nguyen]

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:

String insertQuery = "INSERT INTO users (name, email, password) VALUES (?, ? ,?)";
        // Perform insertion.
        try {       
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(insertQuery);

            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setString(3, pwd);
            int rowCount = pstmt.executeUpdate();

            conn.commit();
            flag = true;
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            //e.printStackTrace();

            System.err.println(
                "There Is Something Wrong. I Don't Know Where. SOS...");
            flag = false;
        } 

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]

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]

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]

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.