SQL中为批量创建的多表添加PK与FK列的技术咨询

当然有简便方法！不用删除重建数据库，我们可以通过动态SQL批量处理给所有目标表添加这两列，还能尽量满足你把它们放在前两列的需求。下面分常见数据库环境详细说明：

1. 批量添加主键列（并设置为主键） #

首先推荐用自增整数类型作为主键（比如INT IDENTITY(1,1)），既保证唯一性又易于维护。我们可以从系统视图sys.tables获取所有用户表，生成批量修改的SQL脚本：

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL += 'ALTER TABLE [' + t.name + '] ADD ID INT IDENTITY(1,1) CONSTRAINT PK_' + t.name + '_ID PRIMARY KEY;' + CHAR(13) + CHAR(10)
FROM sys.tables t
-- 排除不需要处理的表（比如master表）
WHERE t.name != 'master'

-- 先打印脚本确认正确性，没问题再执行
PRINT @SQL
-- EXEC sp_executesql @SQL

2. 批量添加外键列（指向master表） #

接下来添加外键列，并创建外键约束（注意要和master表的主键列类型保持一致，这里假设master表的主键是ID）：

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL += 'ALTER TABLE [' + t.name + '] ADD MasterID INT;' + CHAR(13) + CHAR(10)
+ 'ALTER TABLE [' + t.name + '] ADD CONSTRAINT FK_' + t.name + '_MasterID FOREIGN KEY (MasterID) REFERENCES master(ID);' + CHAR(13) + CHAR(10)
FROM sys.tables t
WHERE t.name != 'master'

PRINT @SQL
-- EXEC sp_executesql @SQL

关于列顺序的说明 #

SQL Server中ALTER TABLE ADD默认会把新列加到表的最后一列，如果只是查询时希望列在前，完全不需要调整物理顺序——查询时手动指定列顺序即可。如果一定要物理上放在前两列，只能通过重建表实现，但风险较高（需要迁移数据、重建索引/触发器等），示例单表操作如下（不推荐批量直接执行，务必先备份）：

-- 示例：调整CapBond表的列顺序
SELECT ID, MasterID, * INTO New_CapBond FROM CapBond
DROP TABLE CapBond
EXEC sp_rename 'New_CapBond', 'CapBond'

MySQL支持直接在添加列时指定位置，操作更简单：

1. 批量添加主键列到第一列 #

SELECT CONCAT(
  'ALTER TABLE `', table_name, '` ADD COLUMN ID INT AUTO_INCREMENT PRIMARY KEY FIRST;'
)
FROM information_schema.tables
WHERE table_schema = '你的数据库名称' AND table_name != 'master';

把查询结果复制出来，确认无误后执行。

2. 批量添加外键列到第二列 #

SELECT CONCAT(
  'ALTER TABLE `', table_name, '` ADD COLUMN MasterID INT AFTER ID; ',
  'ALTER TABLE `', table_name, '` ADD CONSTRAINT FK_', table_name, '_MasterID FOREIGN KEY (MasterID) REFERENCES master(ID);'
)
FROM information_schema.tables
WHERE table_schema = '你的数据库名称' AND table_name != 'master';

同样复制生成的脚本执行即可。

• 先备份数据库：操作前务必全量备份，避免意外出错导致数据丢失。
• 外键类型匹配：外键列MasterID的类型必须和master表的主键列类型完全一致，否则会创建约束失败。
• 已有数据的处理：如果表中已有数据，添加外键前要确保所有行的MasterID值都存在于master表的主键中，否则会触发外键约束错误。可以先给MasterID设置默认值，或者先更新数据后再添加外键。
• 特殊表名处理：像Values这类关键字表名，脚本中已经用方括号（SQL Server）或反引号（MySQL）包裹，确保语法正确。

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