How to differentiate PrimaryKey and uniquekey Constraint in java

Question

In both cases of PrimaryKey violation and uniqueKey violation, I see the same message, error code and sql state values.

When I run the insert query directly in the db, getting the same error message for both the cases. Only difference is the constraint name. we cannot rely on the constraint name all the time. How to differentiate the different types of SQL constraints violations?

try {

      // create Db connection & sql insert into table statement
      // stmt.executeUpdate("insert into mytable  (A,B) .......")
      // mytable has primary key on column A and unique key on column B

}catch(Exception e){
      System.out.println(">>>>>>>>>> get Message :: " + e.getMessage());
      System.out.println(">>>>>>>>>> get err code:: " + ((SQLException)e).getErrorCode());
      System.out.println(">>>>>>>>>> get sql state:: " + ((SQLException)e).getSQLState());      
}

Test 1:

>>>>>>>>>> get Message :: ORA-00001: unique constraint (TABLE_NAME.UK) violated
>>>>>>>>>> get err code:: 1
>>>>>>>>>> get sql state:: 23000

Test 2:

>>>>>>>>>> get Message :: ORA-00001: unique constraint (TABLE_NAME.PK) violated
>>>>>>>>>> get err code:: 1
>>>>>>>>>> get sql state:: 23000

Have you looked up [this](http://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key)? — randominstanceOfLivingThing, Feb 02 '17 at 20:21
@randominstanceOfLivingThing I understand the difference between UK & PK. When error happens I need to identify these errors and handle accordingly. — Vaandu, Feb 02 '17 at 20:24
Why do you want to distinguish between these cases? Isn't it enough information that a unique constraint has been violated? — Mick Mnemonic, Feb 02 '17 at 20:43

score 1 · Answer 1 · answered Feb 02 '17 at 20:30

we cannot rely on the constraint name all the time

And why ?
The constraint name has to be unique.
Consequently to distinguish programmatically from a SQLException a constraint violation from another one which both are the same constraint type and so have the same error code, you could check the constraint name.

If you don't want to use this way, you have to manually check that the data to insert doesn't violate the constraints set on the table.

It is also a way of proceeding but it will be more expensive to perform systematically a check before inserting.

score 1 · Answer 2 · answered Feb 02 '17 at 21:57

If you know the constraint name and the schema owner, you can look up the constraint type from the all_constraints table.

For example:

select constraint_type from all constraints where owner='<your schema owner>'
and constraint_name = '<constraint name returned in your error>'

Look here for the various constraint type codes - https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022

score 0 · Answer 3 · answered Feb 02 '17 at 21:51

Given the name (PK or UK) can you find the meta data about that name?

For example, Oracle has the the ALL_CONSTRAINTS and ALL_INDEXES tables. ALL_CONSTRAINTShas a constraint_type column where P = primary key. ALL_INDEXES has a uniqueness column with values of UNIQUE or NONUNIQUE.

Hopefully your database has something similar.

How to differentiate PrimaryKey and uniquekey Constraint in java

3 Answers3