SQL Server中如何修改系统版本控制时态表的PK约束

你遇到的报错是SQL Server对系统版本控制时态表的强制限制：开启系统版本控制状态下，禁止修改或删除主键约束，必须按以下流程操作： #

操作步骤 #

1. 先临时关闭目标表的系统版本控制，保留历史表数据

ALTER TABLE [dbo].[EmployeesEmployeeDepartments]
SET (SYSTEM_VERSIONING = OFF);

2. 删除原有联合主键约束

ALTER TABLE [dbo].[EmployeesEmployeeDepartments]
DROP CONSTRAINT PK_EmployeesEmployeeDepartments;

3. 新建仅包含EmployeeId的主键约束
   注意：执行前请确保当前表中EmployeeId字段无重复值，否则会创建失败

ALTER TABLE [dbo].[EmployeesEmployeeDepartments]
ADD CONSTRAINT PK_EmployeesEmployeeDepartments PRIMARY KEY (EmployeeId);

4. 重新开启系统版本控制，关联原有的历史表

ALTER TABLE [dbo].[EmployeesEmployeeDepartments]
SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.EmployeesEmployeeDepartmentsHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

优化建议 #

建议把以上所有操作包裹在显式事务中，避免中间步骤出错导致表结构异常，完整事务脚本如下：

BEGIN TRANSACTION;
BEGIN TRY
    -- 关闭系统版本控制
    ALTER TABLE [dbo].[EmployeesEmployeeDepartments] SET (SYSTEM_VERSIONING = OFF);
    
    -- 删除旧主键
    ALTER TABLE [dbo].[EmployeesEmployeeDepartments] DROP CONSTRAINT PK_EmployeesEmployeeDepartments;
    
    -- 新建主键
    ALTER TABLE [dbo].[EmployeesEmployeeDepartments] ADD CONSTRAINT PK_EmployeesEmployeeDepartments PRIMARY KEY (EmployeeId);
    
    -- 重新开启系统版本控制
    ALTER TABLE [dbo].[EmployeesEmployeeDepartments] SET (
        SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.EmployeesEmployeeDepartmentsHistory,
            DATA_CONSISTENCY_CHECK = ON
        )
    );
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

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