如何用H2 SQL(V1.4.196)将列转换为IDENTITY自增主键列？

问题原因 #

在H2数据库V1.4.196版本里，当你直接执行ALTER TABLE trains ALTER COLUMN trno smallint IDENTITY时，H2会默认把IDENTITY列的类型设为BIGINT——哪怕你指定了smallint也会被覆盖。这是因为IDENTITY属性在H2中默认绑定的是BIGINT类型，直接给现有列添加IDENTITY时，类型会被强制替换成默认值。

正确操作方案 #

根据你的场景（原表无主键，要把trno改成带自增的IDENTITY主键且保留SmallInt类型），分两种情况处理：

情况1：表中暂无数据（或可清空数据）

如果表还没存数据，最直接的方式是先删原列，再重新添加带指定类型、IDENTITY属性和主键约束的列：

-- 删除原trNo列
ALTER TABLE t3 DROP COLUMN trNo;
-- 重新添加列，指定SMALLINT类型、自增属性，同时设为主键
ALTER TABLE t3 ADD COLUMN trNo SMALLINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

情况2：表中已有数据，需保留原有内容

如果表中已有数据，不能直接删列，得用临时列过渡：

1. 先添加一个临时自增列（暂时用默认的BIGINT类型）

ALTER TABLE t3 ADD COLUMN temp_trNo BIGINT GENERATED ALWAYS AS IDENTITY;

2. 将原有trNo的值同步到临时列，同时重置自增序列的起始值（假设现有trNo的最大值是100）

UPDATE t3 SET temp_trNo = trNo;
ALTER TABLE t3 ALTER COLUMN temp_trNo RESTART WITH 101;

3. 删除原列，调整临时列的类型、名称并设置主键

ALTER TABLE t3 DROP COLUMN trNo;
ALTER TABLE t3 ALTER COLUMN temp_trNo SET DATA TYPE SMALLINT;
ALTER TABLE t3 RENAME COLUMN temp_trNo TO trNo;
ALTER TABLE t3 ADD PRIMARY KEY (trNo);

额外提示 #

• 尽量使用GENERATED ALWAYS AS IDENTITY这种完整语法，避免用IDENTITY简写带来的类型默认覆盖问题；
• 操作前记得备份数据，避免意外丢失内容。

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