java - 该语句被中止，因为它会导致重复键

Question

我一直收到这个错误:

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

当我运行这段代码时:

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();
    }
}

然后尝试在 netbeans 服务选项卡 -> 数据库中手动插入一条记录:

我确保所有内容都已提交并且连接已正确关闭，所以我不确定为什么 derby 在执行批量插入后继续获取重复索引？

Answer 1

关于GENERATED BY DEFAULT，自动生成的值应该从最后一个存在的ID开始的假设是错误的；它们将始终从 START WITH 值开始。

因此，当您尝试使用 NetBeans 插入另一行时，将首次触发自动 ID 生成，它会尝试插入一个具有 START WITH 值的 ID；因为它是 1，就像您通过 Java 手动输入的 ID 一样，它将因重复键错误而失败。

official documentation还涵盖了您的确切案例:

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.

因此，您要么不手动插入 ID 而只是使用自动生成，要么根据您手动插入的 ID 更改 START WITH，例如 START WITH 2.

您也可以稍后使用 ALTER TABLE 和 RESTART WITH 更改 START WITH 值； documentation of ALTER TABLE有一个例子可以解决与您类似的情况:

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