如何生成删除并重建约束的脚本，修改被85个表引用的Employee表

哈哈，这种被一堆外键绑住没法改表的情况太常见了！我之前处理过比这还多引用的表，给你一套靠谱的方案，分步骤来：

第一步：生成并执行删除外键约束的脚本 #

首先得把所有引用Employee表的外键约束删掉，这里可以直接通过系统视图生成批量删除脚本，不用手动一个个写：

针对SQL Server的脚本 #

-- 生成删除外键约束的脚本
SELECT 
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + '];' AS DropScript
FROM 
    sys.foreign_keys fk
WHERE 
    fk.referenced_object_id = OBJECT_ID('Employee');

执行这个查询后，把结果里的DropScript列内容复制出来，运行这些语句就能删掉所有关联的外键约束。

针对MySQL的脚本 #

SELECT 
    CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name, ';') AS DropScript
FROM 
    information_schema.KEY_COLUMN_USAGE
WHERE 
    referenced_table_name = 'Employee'
    AND referenced_column_name IS NOT NULL;

第二步：修改Employee表结构 #

现在可以放心地修改Employee表了，不管是ALTER修改字段，还是删除重建表都可以。举个删除重建的例子：

-- 先备份数据到临时表
SELECT * INTO Employee_Temp FROM Employee;

-- 删除原表
DROP TABLE Employee;

-- 创建新结构的Employee表（替换成你的新表结构）
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    NewDepartmentColumn VARCHAR(50) -- 新增的字段
    -- 其他字段按你的需求定义
);

-- 把备份数据导回新表
INSERT INTO Employee (EmployeeID, Name, NewDepartmentColumn)
SELECT EmployeeID, Name, NULL -- 注意匹配新表的字段
FROM Employee_Temp;

-- 清理临时表
DROP TABLE Employee_Temp;

如果只是修改字段类型/添加字段，直接用ALTER TABLE语句即可，不用删除重建。

第三步：生成并执行重建外键约束的脚本 #

修改完表结构后，要把之前删掉的外键约束重新建回去，同样用系统视图生成批量脚本：

针对SQL Server的脚本 #

-- 生成重建外键约束的脚本（包含级联规则）
SELECT 
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY (' + 
    STUFF((SELECT ', [' + c.name + ']' 
           FROM sys.foreign_key_columns fkc
           JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
           WHERE fkc.constraint_object_id = fk.object_id
           ORDER BY fkc.constraint_column_id
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
    ') REFERENCES [' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + '] (' + 
    STUFF((SELECT ', [' + c.name + ']' 
           FROM sys.foreign_key_columns fkc
           JOIN sys.columns c ON fkc.referenced_object_id = c.object_id AND fkc.referenced_column_id = c.column_id
           WHERE fkc.constraint_object_id = fk.object_id
           ORDER BY fkc.constraint_column_id
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
    ') ' + 
    CASE WHEN fk.delete_referential_action = 1 THEN 'ON DELETE CASCADE ' ELSE '' END + 
    CASE WHEN fk.update_referential_action = 1 THEN 'ON UPDATE CASCADE ' ELSE '' END + ';' AS CreateScript
FROM 
    sys.foreign_keys fk
WHERE 
    fk.referenced_object_id = OBJECT_ID('Employee');

针对MySQL的脚本 #

-- 生成重建外键约束的脚本（包含级联规则）
SELECT 
    CONCAT('ALTER TABLE ', kcu.table_name, ' ADD CONSTRAINT ', kcu.constraint_name, ' FOREIGN KEY (', kcu.column_name, ') REFERENCES ', kcu.referenced_table_name, '(', kcu.referenced_column_name, ') ',
           CASE WHEN rc.delete_rule = 'CASCADE' THEN 'ON DELETE CASCADE ' ELSE '' END,
           CASE WHEN rc.update_rule = 'CASCADE' THEN 'ON UPDATE CASCADE ' ELSE '' END, ';') AS CreateScript
FROM 
    information_schema.KEY_COLUMN_USAGE kcu
JOIN 
    information_schema.REFERENTIAL_CONSTRAINTS rc ON kcu.constraint_name = rc.constraint_name
WHERE 
    kcu.referenced_table_name = 'Employee'
    AND kcu.referenced_column_name IS NOT NULL;

把生成的CreateScript列内容复制出来执行，就能恢复所有原来的外键约束。

重要注意事项 #

• 一定要先备份数据库！尤其是生产环境，这步绝对不能省，万一出问题能快速回滚。
• 所有脚本先在测试环境跑一遍，确认没有报错再到生产执行。
• 如果你的数据库是其他类型（比如PostgreSQL），可以告诉我，我再给你对应的脚本。

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