Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Java Primary Key Exception due to duplication

  1. Feb 18, 2017 #1
    Hi,
    I have a id field in a table which is delared as a primary key.
    When I am entring the same id again & I am getting following errors:

    MySQLIntegrityConstraintViolationException: Duplicate entry


    I am catching SQLException:
    Code (Java):
    catch(SQLException se){

          //Handle errors for JDBC

          JOptionPane.showMessageDialog(null, "Testing");

          se.printStackTrace();

       }
    The code enters in the catch block.

    Its printing "Testing". So exception is caught. So why i am getting this error. Some body please guide me how to handle this error.


    Zulfi.
     
  2. jcsd
  3. Feb 18, 2017 #2

    jim mcnamara

    User Avatar

    Staff: Mentor

    I'm confused. Does the primary key require uniqueness? SQL thinks it does. It requires every entry in the column of the the table to be unique, and gives you an error: 'No duplicates allowed'.

    Either change the table's metadata (change the field definition) or stop trying to insert duplicate (meaning it already is in the table as a primary key value) data.

    Those are your choices. Your code in production should tell users what exactly happened. Not 'testing', but a sentence that explains why they got an error, and how they can avoid it in the future. This will prevent late night calls from panicked users who waited far too late to do something for the boss and now cannot do it.
     
  4. Feb 18, 2017 #3

    DaveC426913

    User Avatar
    Gold Member

    Maybe I'm second-guessing your intent but why would you allow manual entry of an id that needs to be unique?
    If the user has no control over the id, why let them manipulate it at all? It should be system-generated and invisible to the user.
     
  5. Feb 19, 2017 #4
    Hi,
    My program is not crashing but the output shows the messages which I want to avoid.
    This occurs when I give the id value of '10' which is already present in the db. I found such error messages at other sites like:
    https://developer.jboss.org/thread/23955?_sscc=t
    http://stackoverflow.com/questions/26761436/catch-duplicate-key-insert-exception

    But there is no proper solution. I can avoid entry 10 because I know its already there but what when some body uses my code & faces this problem. I want to print a good message on the screen instead of displaying these scary messages.

    Zulfi.
     
  6. Feb 19, 2017 #5

    Borg

    User Avatar
    Science Advisor
    Gold Member

    You have three choices.
    1. Don't let users set the primary key so that the database creates a new ID each time.
    2. Check the supplied ID to see if it's already in the database before the code attempts an insert. If it's already there, perform an update instead of an insert.
    3. Let it throw an error like it's doing currently but with a better message.
    It's difficult to know what to tell you without knowing your requirements.
     
  7. Feb 19, 2017 #6

    DaveC426913

    User Avatar
    Gold Member

    Yes, there is a proper solution.

    Record ID should be system-assigned. User should have no input on record ID.
     
  8. Feb 19, 2017 #7

    jack action

    User Avatar
    Science Advisor
    Gold Member

  9. Feb 19, 2017 #8
    Hi,
    Thanks for all responses.
    <
    Record ID should be system-assigned. User should have no input on record ID.>
    I can do that but then there wont be any learning. I cant stop students from asking questions. Control enters in the catch block & why it prints the nasty message. I want to see how can i avoid that message.
    >
    <
    Let it throw an error like it's doing currently but with a better message.>
    yes i want to chose this option. Kindly tell me the solution.I would try jack's solution.
    Zulfi.
     
  10. Feb 19, 2017 #9

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

    But the most important thing to learn is that the system should generate a valid new id. What happens if a user enters an already used id and some processes complete while others don't? Your data base could become riddled with bad data.
    You need to make sure this error is trapped and the process terminated before any database operations are performed. Otherwise, the process may partially update data and corrupt the data base.
     
  11. Feb 21, 2017 #10
    Hi,
    <
    But the most important thing to learn is that the system should generate a valid new id. What happens if a user enters an already used id and some processes complete while others don't? Your data base could become riddled with bad data.

    Reference https://www.physicsforums.com/threads/primary-key-exception-due-to-duplication.904571/
    >
    It should be caught in the exception but its not working that's what i want to know i.e. what's the reason of this problem.

    Zulfi.
     
  12. Feb 21, 2017 #11

    Borg

    User Avatar
    Science Advisor
    Gold Member

    Exceptions should not be used as a normal routine in your code - they are for catching and handling errors and shouldn't be used as part of the expected flow of the software. The reason for the problem is that your table likely has a unique key setting on the ID column. If the software attempts to write a duplicate ID, the database will reject it and throw an error. You have to write a block of code that checks to see if the ID already exists before you attempt to write any ID to the database. What you do after that is based on your requirements.
     
  13. Feb 21, 2017 #12

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

    The long printout is the stack trace from the se.printStackTrace() line in your exception handler. That handler should do something more to avoid problems later in the program.
     
  14. Feb 21, 2017 #13
    Hi,
    <The long printout is the stack trace from the se.printStackTrace() line >


    Thanks. It worked. But i dont know how can be sure in the cacth block that its a duplicate key error. There can be other sql exceptions also.
    Jack: Sorry i tried your suggestion but it says class
    DataIntegrityViolationException not found.

    Their work was not authentic.
    <Exceptions should not be used as a normal routine in your code >
    In my view its normal in java. We cant skip the exception code. The data base access code may cause exceptions so we have to put it into a catch block.
    Zulfi.
     
  15. Feb 21, 2017 #14

    Borg

    User Avatar
    Science Advisor
    Gold Member

    You're not understanding what I wrote. Having an exception block is an accepted process. Using it the way that you're attempting to use it, isn't. In an ideal world, you write your code so that it does not throw any errors. If you've written your code well, it should never throw an error.
     
  16. Feb 21, 2017 #15
    Hi,
    Okay you are right when you say ideal. For people like me, java has provided exception handling mechanism. Actually i am doing it for the sake of learning. As a result of this example, I am able to understand the problem in my code which i have commented & i thanked FactChecker for this. I think we must explore (about our confusions) even if we know the correct way of writing code & share our problems with others through forum.

    Zulfi.
     
  17. Feb 21, 2017 #16

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

  18. Feb 21, 2017 #17

    jack action

    User Avatar
    Science Advisor
    Gold Member

    Which solution did you use? This one:
    Code (Java):

    try {
        ...
    } catch (Exception e) {

        if (e instanceof SQLIntegrityConstraintViolationException) {
              // duplicate record or alike problem
        }
    }
    Or this one:
    Code (Java):

    catch (SQLException ex) {
        if (ex.getSQLState().startsWith("23")) {
            JOptionPane.showMessageDialog(null, "Duplicate");
        }
    }
    Because there are no references to class DataIntegrityViolationException in either of these examples.
     
  19. Feb 21, 2017 #18
    Hi all,
    Thanks Jack. Your code snippet is working.

    catch(SQLException se){
    //Handle errors for JDBC
    //JOptionPane.showMessageDialog(null, "Testing");
    //se.printStackTrace();
    if (se.getSQLState().startsWith("23")) { JOptionPane.showMessageDialog(null, "Duplicate");
    }


    }
    I now think that i have solved this problem.

    Zulfi.
     
  20. Feb 25, 2017 #19
    Hi,
    Following is the correct answer to my post. I think SQLState is not the correct way to find the specific error related to the exceptions. Instead we must use Error number. So the correct code for handling the duplicate key error should use the error number & SQLState because SQLSTATE 23000 is a broader error directive:
    Based upon above my modified code is :

    catch(SQLException se){

    //Handle errors for JDBC

    //JOptionPane.showMessageDialog(null, "Testing");

    //se.printStackTrace();

    int errCode = se.getErrorCode();

    JOptionPane.showMessageDialog(null, "ErrorCode = " + errCode + "\n");

    if(errCode == 1062)

    JOptionPane.showMessageDialog(null, "Duplicate entry for id = " + id.getText() + "\n");

    else if(errCode == 1169)

    JOptionPane.showMessageDialog(null, "Can't write, because of unique constraint, to table registeration" );

    else

    JOptionPane.showMessageDialog(null, "Find erro associated with error code from the link: https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html" );

    //if (se.getSQLState().startsWith("23")) { JOptionPane.showMessageDialog(null, "Duplicate"); }

    //several errors arerelated to SQLState 23000 so we must grab the particular error number & then //compose our message using the error number.


    }

    Thanks all for your interest in my problem.

    Zulfi.
     
  21. Feb 26, 2017 #20

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

    Yes, you can usually get detailed information about an error that generates an exception and display specific messages. But you seem to be ignoring some good basic advice regarding the use of a unique key in a database. Suit yourself.

    PS. You may just want to trap the exception and display the error code. People can look up codes and you don't have to translate them all. There may be a lot (sometimes hundreds). There is often some text supplied in a variable that identifies the error.
     
    Last edited: Feb 26, 2017
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Primary Key Exception due to duplication
  1. Exception Handling (Replies: 7)

  2. Exception in C++ (Replies: 6)

Loading...