SQL修改school_student表主键为自增时遇identity语法错误，求解决

错误原因 #

SQL Server不支持直接通过ALTER COLUMN语句给已存在的列添加IDENTITY属性，这是语法层面的限制，所以你执行的语句会报错Incorrect syntax near the keyword 'identity'。

解决方案 #

根据表中是否有数据，提供两种可行方案：

方案1：表中无数据（或可清空数据）

直接删除原主键和列，重新添加带自增属性的主键列：

-- 先删除原主键约束（约束名需替换为实际名称，可执行sp_help school_student查看）
ALTER TABLE school_student DROP CONSTRAINT PK__school_s__3213E83FXXXXXX;
-- 删除原id列
ALTER TABLE school_student DROP COLUMN id;
-- 添加带自增的id列并设置为主键
ALTER TABLE school_student ADD id INT NOT NULL IDENTITY(5,1) PRIMARY KEY;

方案2：表中有数据需保留

通过临时表迁移数据，避免数据丢失：

-- 1. 创建临时表，id设为自增主键
CREATE TABLE school_student_temp (
    id INT NOT NULL IDENTITY(5,1) PRIMARY KEY,
    name VARCHAR(20),
    DOB Date,
    Address VARCHAR(50),
    phone_no INT
);

-- 2. 开启自增列插入权限，将原表数据导入临时表（如需保留原id值则执行此步骤，否则跳过SET语句直接插入）
SET IDENTITY_INSERT school_student_temp ON;
INSERT INTO school_student_temp (id, name, DOB, Address, phone_no)
SELECT id, name, DOB, Address, phone_no FROM school_student;
SET IDENTITY_INSERT school_student_temp OFF;

-- 3. 删除原表
DROP TABLE school_student;

-- 4. 将临时表重命名为原表名
EXEC sp_rename 'school_student_temp', 'school_student';

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