SQL Server Azure v12中批量更新主键及关联外键的方法咨询

一、批量增加主键与关联外键值的步骤 #

要将Residents.Id及所有关联外键值增加10000，按以下顺序操作：

1. 禁用所有关联外键约束 #

SQL Server的外键约束会阻止修改主键值，先禁用所有关联到Residents.Id的外键（仅禁用检查逻辑，不删除约束本身）：

-- 生成禁用外键的脚本，执行后即可禁用所有关联外键
SELECT 
    'ALTER TABLE ' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) + 
    ' NOCHECK CONSTRAINT ' + fk.name + ';'
FROM sys.foreign_keys fk
JOIN sys.tables t ON fk.referenced_object_id = t.object_id
WHERE t.name = 'Residents' AND SCHEMA_NAME(t.schema_id) = 'dbo'; -- 替换为你的实际schema名称

2. 修改主键列的值 #

由于Residents.Id是Identity自增列，需先开启手动修改权限，再更新值：

-- 开启手动修改Identity列的权限
SET IDENTITY_INSERT Residents ON;

-- 将所有主键值增加10000
UPDATE Residents SET Id = Id + 10000;

-- 关闭身份插入权限
SET IDENTITY_INSERT Residents OFF;

3. 修改所有关联表的外键值 #

遍历所有关联表，将对应外键列的值同步增加10000。例如关联表Orders的外键是ResidentId：

UPDATE Orders SET ResidentId = ResidentId + 10000;

重复此操作，覆盖所有关联外键列。

4. 重置Identity种子值（可选） #

如果需要保留Residents.Id的自增特性，将种子值重置为当前最大主键值+1，避免后续自增出现重复值：

DBCC CHECKIDENT ('Residents', RESEED, (SELECT MAX(Id) FROM Residents));

5. 重新启用外键约束 #

执行以下脚本生成启用外键的语句，恢复约束检查：

SELECT 
    'ALTER TABLE ' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) + 
    ' CHECK CONSTRAINT ' + fk.name + ';'
FROM sys.foreign_keys fk
JOIN sys.tables t ON fk.referenced_object_id = t.object_id
WHERE t.name = 'Residents' AND SCHEMA_NAME(t.schema_id) = 'dbo';

二、关于SSMS修改IsIdentity属性导致外键删除的问题 #

1. 是否正常？ #

这是SSMS表设计器的默认行为，并非SQL Server本身的特性。当你在表设计器中修改Identity属性时，SSMS会创建临时新表、迁移数据、删除原表，再将新表重命名为原表名。这个过程中外键约束不会被自动迁移，因此会被删除。

2. 无需删除重建外键的替代方法 #

完全不需要通过表设计器修改，直接用T-SQL操作即可保留外键：

• 如果只是需要修改Identity列的值：用前面提到的SET IDENTITY_INSERT方法，不需要取消Identity属性。
• 如果确实需要取消Identity属性：用以下T-SQL语句（不会删除外键）：

-- 先删除主键约束（主键名称需自行查询，例如PK_Residents）
ALTER TABLE Residents DROP CONSTRAINT PK_Residents;

-- 修改列属性，取消Identity
ALTER TABLE Residents ALTER COLUMN Id INT NOT NULL; -- 根据实际数据类型调整

-- 重新添加主键约束
ALTER TABLE Residents ADD CONSTRAINT PK_Residents PRIMARY KEY CLUSTERED (Id);

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