Primary Key Exception due to duplication

[zak100]

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 entryI am catching SQLException:

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.

 

[jim mcnamara]

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.

 

[DaveC426913]

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.

 

[zak100]

Hi,
My program is not crashing but the output shows the messages which I want to avoid.

Connecting to database...
Creating statement...
Connecting to database...
Creating statement...
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '10' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
at primaryKeyTestFrame.jButton1ActionPerformed(primaryKeyTestFrame.java:234)
at primaryKeyTestFrame.access$000(primaryKeyTestFrame.java:17)
at primaryKeyTestFrame$1.actionPerformed(primaryKeyTestFrame.java:135)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6535)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6300)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4891)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2750)
at java.awt.Component.dispatchEvent(Component.java:4713)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

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.

 

[Borg]

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

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.

 

[DaveC426913]

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.

Yes, there is a proper solution.

Record ID should be system-assigned. User should have no input on record ID.

 

[jack action]

Stackoverflow to the rescue ...

 

[zak100]

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 won't be any learning. I can't 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.

 

[FactChecker]

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 won't be any learning.

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.

>
<
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.

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.

 

[zak100]

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.

 

[Borg]

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.

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.

 

[FactChecker]

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.

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.

 

[zak100]

Hi,
<The long printout is the stack trace from the se.printStackTrace() line >Thanks. It worked. But i don't 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 can't skip the exception code. The data base access code may cause exceptions so we have to put it into a catch block.
Zulfi.

 

[Borg]

<Exceptions should not be used as a normal routine in your code >
In my view its normal in java. We can't skip the exception code. The data base access code may cause exceptions so we have to put it into a catch block.

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.

 

[zak100]

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.

 

[FactChecker]

Before proceeding with a user-input key, you should test if it already exists. You can use "Count" to see if it already exists without throwing any exceptions for a repeated key. (see http://stackoverflow.com/questions/...cific-primary-key-exists-in-a-mysql-table-fro )

 

[jack action]

Jack: Sorry i tried your suggestion but it says class
DataIntegrityViolationException not found.

Which solution did you use? This one:

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

    if (e instanceof SQLIntegrityConstraintViolationException) {
          // duplicate record or alike problem
    }
}

Or this one:

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.

 

[zak100]

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.

 

[zak100]

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:

. If you look at the list of error codes for MySql you will find that there is more than one that 'startsWith("23").

https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html

Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d



Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE)

Message: Can't write, because of unique constraint, to table '%s'



Error: 1216 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW)

Message: Cannot add or update a child row: a foreign key constraint fails



Error: 1217 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED)

Message: Cannot delete or update a parent row: a foreign key constraint fails



Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2)

Message: Cannot delete or update a parent row: a foreign key constraint fails (%s)



Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2)

Message: Cannot add or update a child row: a foreign key constraint fails (%s)



Error: 1557 SQLSTATE: 23000 (ER_FOREIGN_DUPLICATE_KEY_OLD_UNUSED)

Message: Upholding foreign key constraints for table '%s', entry '%s', key %d would lead to a duplicate entry



Error: 1586 SQLSTATE: 23000 (ER_DUP_ENTRY_WITH_KEY_NAME)

Message: Duplicate entry '%s' for key '%s'

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.

 

[FactChecker]

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.