The statement was aborted because it would have caused a duplicate key

Question

I keep getting this error:

Error code 20000, SQL state 23505
Insert command failed: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL160524112023610' defined on 'TEST'.

When I run this code:

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DerbyBatch {

    private static Connection connection;

    public static void main(String args[]) {
        try {
            createDatabase();
            createTable();
            insertBatch();
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(DerbyBatch.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void createDatabase() throws SQLException {
        connection = DriverManager.getConnection("jdbc:derby:" + new File("test").getAbsolutePath() + ";" + "create=true");
        disconnect();
    }

    public static void createTable() throws ClassNotFoundException, SQLException {
        connect();
        String createTable = "CREATE TABLE \"APP\".\"TEST\" (ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), TEXT VARCHAR (30000) NOT NULL, PRIMARY KEY (ID))";
        PreparedStatement preparedStatement = connection.prepareStatement(createTable);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.commit();
        disconnect();
    }

    public static void insertBatch() throws SQLException, ClassNotFoundException {
        connect();
        String sql = "INSERT INTO TEST (ID, TEXT) VALUES (?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 1);
        preparedStatement.setString(2, "TEST");
        preparedStatement.addBatch();
        preparedStatement.executeBatch();
        connection.commit();
        disconnect();
        Logger.getLogger(DerbyBatch.class.getName()).log(Level.SEVERE, "All data inserted.");
    }

    public static void connect() throws ClassNotFoundException, SQLException {
        Class.forName("net.sf.log4jdbc.DriverSpy");
        Connection temp = DriverManager.getConnection("jdbc:log4jdbc:derby:" + new File("test").getAbsolutePath());
        connection = new net.sf.log4jdbc.ConnectionSpy(temp);
        connection.setAutoCommit(false);
    }

    public static void disconnect() throws SQLException {
        connection.close();
    }
}

and then try to manually insert a record in netbeans services tab -> databases:

I made sure that everything is committed and the connection is closed properly so I'm not sure why derby keeps picking up a duplicate index after doing a batch insert?

Answer 1

Regarding GENERATED BY DEFAULT, the assumption that automatically generated values should start from the last existing ID is wrong; they will always start from the START WITH value.

So, when you try to insert another row with NetBeans, the automatic ID generation will get triggered for the first time and it will try to insert an ID with the START WITH value; since that is 1 like the ID you manually put via Java, it will fail with the duplicate key error.

The official documentation also covers your exact case:

create table greetings 
        (i int generated by default as identity, ch char(50));
-- specify value "1":
insert into greetings values (1, 'hi');
-- use generated default 
insert into greetings values (DEFAULT, 'salut');
-- use generated default 
insert into greetings(ch) values ('bonjour');  

Note that unlike a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee uniqueness.

Thus, in the above example, the hi and salut rows will both have an identity value of 1, because the generated column starts at 1 and the user-specified value was also 1.

To prevent duplication, especially when loading or importing data, create the table using the START WITH value which corresponds to the first identity value that the system should assign.

So you either don't manually insert IDs and just use the automatic generation, or you change the START WITH accordingly to your manually inserted ID, for example START WITH 2.

You can also change the START WITH value at a later moment using ALTER TABLE with RESTART WITH; the documentation of ALTER TABLE has an example that address a situation similar to yours:

Consider the following example, which involves a combination of automatically generated data and manually inserted data:

CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2

The system will automatically generate values for the identity column. But now you need to manually insert some data into the identity column:

INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)

The identity column has used values 1 through 5 at this point. If you now want the system to generate a value, the system will generate a 3, which will result in a unique key exception because the value 3 has already been manually inserted.

To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6