Guntram
Guntram

Reputation: 414

java jdbc duplicate entry for key primary on insert foreign key

The "duplicate entry for key 'primary'" seems to be a popular problem on the internet, even though I did not manage to find a post related to my problem. I'm trying to insert values into a table using java jdbc, but somehow I get this 'primary' key problem.

The relevant code is this:

preparedStatement = connect
              .prepareStatement("insert into database1.table2 values (default, ?, ?));
          preparedStatement.setString(1, randomString(12)); // Inserts a normal String, tested & working
          insert_plz(plz); // Inserts a given value as a primary key into table1
          preparedStatement.setInt(2, plz); // Inserts that value as a foreign key 
          preparedStatement.executeUpdate(); // Exception is thrown here

Calling function insert_plz(plz) works without any problems, I checked the database.

This is how I created table2:

create table table2 (
standorte_schluessel int unsigned auto_increment,
name varchar (20) not null,    
postleitzahl int unsigned,
primary key (standorte_schluessel),
foreign key (postleitzahl) references plz (postleitzahl) on delete cascade
);

Now my question is, how come that on trying to insert a foreign key, it gives me a primary key error?

Also, I'm kind of new to mysql (not to java though) so the answer to my question might be obvious (unfortunately not to me).

This is the error log:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '74141' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
at Connect.insert_standort(Connect.java:113)
at Connect.readDataBase(Connect.java:41)
at Main.main(Main.java:5)

Upvotes: 2

Views: 19283

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108988

You should explicitly specify the columns and use the following insert query:

insert into database1.table2(name, postleitzahl) values (?, ?)

See Using AUTO_INCREMENT.

I am not 100% sure, but I suspect that using DEFAULT on the position of the id column might insert the current autoincrement value, instead of the next autoincrement.

Upvotes: 2

Related Questions