Oracle 21c自增主键表插入报错ORA-01400及ID跳号求助

问题现象 #

• 发送POST请求插入数据时触发ORA-01400错误，提示无法将NULL插入"C##USER"."COUNTRIES"."COUNTRY_NAME"字段；
• 控制器中硬编码country_name值时数据可成功插入，但自增字段country_id出现跳号（预期为2却变为22）。

相关信息 #

表结构 #

CREATE TABLE countries 
(
    country_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, 
    country_name VARCHAR(255) NOT NULL
);

POST请求体 #

{"country_name" = "PK"}

报错信息 #

Error executing SQL query: Error: ORA-01400: cannot insert NULL into ("C##USER"."COUNTRIES"."COUNTRY_NAME")
at Protocol._processMessage (C:\Users\HP\Desktop\Ticketeer\node_modules\oracledb\lib\thin\protocol\protocol.js:172:17)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async ThinConnectionImpl._execute (C:\Users\HP\Desktop\Ticketeer\node_modules\oracledb\lib\thin\connection.js:195:7)
at async ThinConnectionImpl.execute (C:\Users\HP\Desktop\Ticketeer\node_modules\oracledb\lib\thin\connection.js:927:14)
at async Connection.execute (C:\Users\HP\Desktop\Ticketeer\node_modules\oracledb\lib\connection.js:861:16)
at async Connection. (C:\Users\HP\Desktop\Ticketeer\node_modules\oracledb\lib\util.js:165:14)
at async AddNewCountry (C:\Users\HP\Desktop\Ticketeer\backend\controller\countriesController.js:126:13) {
offset: 46,
errorNum: 1400,
code: 'ORA-01400'
}

AddNewCountry接口代码 #

AddNewCountry: async function (req, res){
        let connection ;
        try {
 
          connection = await getConnection();
            const query = `INSERT INTO countries (country_name) VALUES (:1)`;
            const binds = [req.body.country_name];
            const options = {
              autoCommit: true, 
            };
            
            await connection.execute(query,binds,options);
            res.status(202).send("Added");
        } 
        catch (error) {
            console.error('Error executing SQL query:', error);
            res.status(500).send('Internal Server Error');
          
        } 
        finally {
            if (connection) {
              try {
                // Release the connection when done
                await connection.close();
              } catch (error) {
                console.error('Error closing database connection:', error);
              }
            }
        }
    },

解决方案 #

1. 修复ORA-01400错误 #

• 修正请求体格式：当前请求体使用=而非:，不符合JSON规范，导致后端无法解析到country_name参数，最终传入NULL。正确的请求体应为：

  {"country_name": "PK"}
  

• 验证请求解析：确保后端已配置JSON解析中间件（如Express的express.json()），可在接口中添加console.log(req.body)打印请求参数，确认参数是否被正确解析。

2. 解决自增主键跳号问题 #

Oracle的IDENTITY列默认预分配20个值的缓存，当数据库重启、连接池回收连接时，未使用的缓存值会被丢弃，导致ID跳号。解决方式：

• 建表时指定NO CACHE禁用缓存：

  CREATE TABLE countries 
  (
      country_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO CACHE) PRIMARY KEY, 
      country_name VARCHAR(255) NOT NULL
  );
  

• 若表已创建，可通过ALTER语句修改：

  ALTER TABLE countries MODIFY country_id GENERATED ALWAYS AS IDENTITY (NO CACHE);
  

  注：禁用缓存会略微降低插入性能，但能保证ID连续递增。

内容的提问来源于stack exchange，提问作者Mallick