com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'

Question

I am getting this exception from today onwards. Yesterday things were fine. I will post my code :

public void enterStaff() throws ClassNotFoundException, SQLException {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/project?"
                        + "user=root&password=virus");
        statement = connect.createStatement();

        preparedStatement = connect
                .prepareStatement("SELECT count(*)FROM information_schema.tables\n"
                        + "WHERE table_schema = 'project' AND table_name = 'staff'");
        rs = preparedStatement.executeQuery();
        rs.next();
        int chk = rs.getInt(1);

        if (chk != 1) {
            preparedStatement = connect
                    .prepareStatement("create table staff (staffname varchar(30) primary key);");
            preparedStatement.executeUpdate();
        }

        preparedStatement = connect
                .prepareStatement("insert into staff values(?);");
        preparedStatement.setString(1, addSubName.getText());

        preparedStatement.executeUpdate();
    } catch (ClassNotFoundException | SQLException e) {
        throw e;
    } finally {
        close2();
    }

}

private void close2() {
    try {

        if (statement != null) {
            statement.close();
        }

        if (connect != null) {
            connect.close();
        }
    } catch (SQLException e) {

    }
}

I am trying to create a table with only one column and that column is the primary key. When the code is run for the first time the table is getting created. But value is not getting entered. No exception is shown then. But when I try to enter value for the second time, the exception comes up.

How can I correct this ? If anyone needs to see some more code I will post it.

This is the code through which the user enters the data :

    addSubName = new TextField();
    addSubName.setPromptText("Staff Name");
    addSubName.setPrefSize(200, 30);

    final Button b2 = new Button("Add");
    b2.setFont(Font.font("Calibri", FontWeight.BOLD, 17));
    b2.setPrefSize(70, 30);
    b2.setStyle(" -fx-base: #0066ff;");
    b2.setTextFill(Color.BLACK);
    b2.setOnAction(new EventHandler<ActionEvent>() {
        @Override
        public void handle(ActionEvent e) {

            data.add(new Staff(addSubName.getText()));
            addSubName.clear();

            try {
                enterStaff();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(AddStaff.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                Logger.getLogger(AddStaff.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    });

The exception details is :

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
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.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at addstaff.AddStaff.enterStaff(AddStaff.java:149)
at addstaff.AddStaff$2.handle(AddStaff.java:100)
at addstaff.AddStaff$2.handle(AddStaff.java:92)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:69)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:28)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Node.fireEvent(Node.java:6867)
at javafx.scene.control.Button.fire(Button.java:179)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:193)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:336)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:329)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:64)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:33)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3311)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3151)
at javafx.scene.Scene$MouseHandler.access$1900(Scene.java:3106)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1563)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2248)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:250)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:173)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:292)
at com.sun.glass.ui.View.handleMouseEvent(View.java:530)
at com.sun.glass.ui.View.notifyMouse(View.java:924)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$100(WinApplication.java:17)
at com.sun.glass.ui.win.WinApplication$3$1.run(WinApplication.java:67)
at java.lang.Thread.run(Thread.java:744)

Can we see the values you're inserting? Your create statement's got no auto_increment or anything of the sort, so maybe the insert values are messing up. — domdomcodecode
– domdomcodecode, Commented Apr 8, 2014 at 2:25
Some example insert values, or the code that generates/determines the values you're putting in the insert statement. — domdomcodecode
– domdomcodecode, Commented Apr 8, 2014 at 2:27
The value you tried to submit through the text box must already be present in the table. You might have clicked Add button more than once. — Ravinder Reddy
– Ravinder Reddy, Commented Apr 8, 2014 at 2:34

jmail · Accepted Answer · 2014-04-08 09:15:00Z

1

you shoud remove the ; in your sql query:

wrong query:

prepareStatement("create table staff (staffname varchar(30) primary key);");

preparedStatement = connect.prepareStatement("insert into staff values(?);");

correct query is:

prepareStatement("create table staff (staffname varchar(30) primary key)");

preparedStatement = connect.prepareStatement("insert into staff values(?)");

CHECK YOUR INSERT QUERY

And, I think your INSERT the values in the db, that query is too wrong, did mention the tablename only, you should not mention the columnname. so you should must add the tablename with your query.

wrong query:

insert into staff values(?)

correct query:

    INSERT INTO table_name
    VALUES (value1,value2,value3,...);

refer this link:

http://www.w3schools.com/sql/sql_insert.asp

UPDATE:1

wrong code:

statement = connect.createStatement();
preparedStatement = connect
                .prepareStatement("SELECT count(*)FROM information_schema.tables\n"
                        + "WHERE table_schema = 'project' AND table_name = 'staff'");
        rs = preparedStatement.executeQuery();
        rs.next();

you should change like:sample

Class.forName(driverName).newInstance();
con=DriverManager.getConnection(connectionUrl+dbName,user,password);
st = con.createStatement();
String sql="SELECT * FROM employees";
rs=st.executeQuery(sql);

Note:

you should not call the sql query.
And, you are using preparedStatement to call, you should must chage to statement
the preparedStatement is can't resolved.

like,

string sql=....sql query...;
statement.executeQuery(sql)

update:2:-->sample

public class User 
{
private String empname;
public String getEmpName()
{
    return empname;
}
public void setEmpName(String empname)
{
    this.empname=empname;
}

public void addUser(User user)
{
    try
    {
        PreparedStatement ps;
        ps=connection.prepareStatement("INSERT INTO employee (empname,empaddress,depname) VALUES (?,?,?)");
        ps.setString(1, user.getEmpName());
        ps.setString(2, user.getEmpAddress());
        ps.setString(3, user.getDepName());
        ps.executeUpdate();
    }
    catch(Exception e)
    {
        System.out.println(e);
    }
}

edited Apr 8, 2014 at 9:15

answered Apr 8, 2014 at 5:51

jmail

6,1503 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

jmail Over a year ago

@Tom, check this ansswer

TomJ Over a year ago

It doesn't worked. Did you noticed the exception ? It says duplicate value for primary key. The exception is coming at the second time of entry, not at the first time. That means the data is not getting entered into the database. Why data is not getting entered into the database ?

TomJ Over a year ago

In the 'b2.setOnAction(new EventHandler<ActionEvent>() ' I first wrote the ' addSubName.clear(); '. Then I wrote the ' enterStaff() ' method. So automatically it will get an empty value at the time of insertion to the table in the database. So I called ' enterStaff() ' method first and then called ' addSubName.clear() ' method. But this too don't worked.

TomJ Over a year ago

How can I change this - preparedStatement.setString(1, msg);

TomJ Over a year ago

Which part in that referred question is helpful to me ?

|

Ithar · Accepted Answer · 2016-06-16 09:23:01Z

A possible cause for this error is that you are trying to enter the same value on a primary key filed into a table.

For example if you have a table users where the primary key is user_id and you try to entry the following:

INSERT INTO users ('user_id', 'name', 'age') VALUES (112233, 'Tom', 18);
INSERT INTO users ('user_id', 'name', 'age') VALUES (112233, 'Barry', 21);

A MySQLIntegrityConstraintViolationException: Duplicate entry error will arise.

Note: there could be certain valid cases where one needs to insert or update a column in the a table and in such cases one can use ON DUPLICATE KEY UPDATE.

Example:

INSERT INTO social_link ('user_id', 'social_id') VALUES (112233, 59874) ON DUPLICATE KEY UPDATE social_id=575123;

Collectives™ on Stack Overflow

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related