Catch duplicate key insert exception

Question

I have a table with a unique primary key column called id. Sometimes when I perform an INSERT query I get an error because the id value is already used.

Can I catch this specific error with try and catch?

In addition to what @sp00m asked, which exception class did you put in your catch block? — AdamMc331, Nov 05 '14 at 15:59
You shouldn't be catching it as you should really only catch `Exceptions` in Java — Lucas, Nov 05 '14 at 15:59
you can check primary key exist or not before insert and give a error — Madhawa Priyashantha, Nov 05 '14 at 16:01
I doing some task simultaneously. What if the PK does not exist when I checking it but on INSERT it exists? — Josh, Nov 05 '14 at 16:03
Which error is thrown? Could you please provide the try/catch you used and the stacktrace? — sp00m, Nov 05 '14 at 16:03
read this one http://stackoverflow.com/questions/1988570/how-to-catch-a-specific-exceptions-in-jdbc — Madhawa Priyashantha, Nov 05 '14 at 16:03
@fastsnail sure, but wouldn't that be less efficient to have to dig through the table each time you want to insert? — AdamMc331, Nov 05 '14 at 16:03

score 15 · Accepted Answer · answered Nov 05 '14 at 16:04

15

Looks like mysql is throwing 1062 error code for duplicate primary key. You can check the error code for your sql exception :

public static final int MYSQL_DUPLICATE_PK = 1062;

try{
    //code that throws sql exception
} catch(SQLException e){
    if(e.getErrorCode() == MYSQL_DUPLICATE_PK ){
        //duplicate primary key 
    }
}

Notice that this approach is not cross database vendor, because different vendors might have different error codes for duplicate PK.

answered Nov 05 '14 at 16:04

Daniel

1,861
1
15
23

I thought `SqlException` was the class but wasn't sure. Great idea on checking for specific error code - I had no idea that was possible. – AdamMc331 Nov 05 '14 at 16:05
2

For completeness - Under Oracle I believe the error code returned is `1` [see here](http://www.dba-oracle.com/sf_ora_00001_unique_constraint_violated.htm). – OldCurmudgeon Nov 05 '14 at 16:07

Catch duplicate key insert exception

1 Answers1

Linked