如何从原数据库自动导入PK/FK约束至新数据库？

原数据库「sndpro copy」因排序规则问题，需将数据迁移至新数据库「sndpro」。已完成架构和数据迁移，但主键（PK）、外键（FK）约束未同步，需要编写脚本从原库获取约束信息，自动在新库添加这些约束。自行编写及ChatGPT 3.5生成的脚本均未生效，原脚本如下：

USE sndpro_copy
GO

DECLARE @table_name NVARCHAR(MAX)
DECLARE @constraint_name NVARCHAR(MAX)
DECLARE @constraint_type NVARCHAR(MAX)
DECLARE @referenced_table_name NVARCHAR(MAX)
DECLARE @referenced_constraint_name NVARCHAR(MAX)

DECLARE cursor_tables CURSOR FOR
    SELECT 
        t.name,
        c.name,
        c.type_desc,
        OBJECT_NAME(fkc.referenced_object_id) as referenced_object_name,
        rc.name
    FROM 
        sys.tables t
        left JOIN sys.key_constraints c ON c.parent_object_id = t.object_id
        left JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = t.object_id AND fkc.constraint_object_id = c.object_id
        left JOIN sys.objects r ON r.object_id = fkc.referenced_object_id
        left JOIN sys.key_constraints rc ON rc.object_id = fkc.referenced_object_id
    WHERE 
        c.type_desc IN ('PRIMARY_KEY_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT')
    ORDER BY 
        t.name

OPEN cursor_tables

FETCH NEXT FROM cursor_tables INTO @table_name, @constraint_name, @constraint_type, @referenced_table_name, @referenced_constraint_name

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    IF @constraint_type = 'PRIMARY_KEY_CONSTRAINT'
    BEGIN
        SET @sql = 'ALTER TABLE ' + QUOTENAME('centegy_sndpro_uet.' + @table_name) + ' ADD CONSTRAINT ' + QUOTENAME('PK_' + @table_name) + ' PRIMARY KEY ('

        SELECT @sql = @sql + QUOTENAME(c.name) + ','
        FROM sys.index_columns ic
        JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
        WHERE ic.object_id = OBJECT_ID(@table_name) AND ic.index_id = 1
        ORDER BY ic.key_ordinal

        SET @sql = LEFT(@sql, LEN(@sql) - 1) + ')'
    END
    ELSE IF @constraint_type = 'FOREIGN_KEY_CONSTRAINT'
    BEGIN
        SET @sql = 'ALTER TABLE ' + QUOTENAME('centegy_sndpro_uet.' + @table_name) + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name) + ' FOREIGN KEY ('

        SELECT @sql = @sql + QUOTENAME(c.name) + ','
        FROM sys.foreign_key_columns fkc
        JOIN sys.columns c ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
        WHERE fkc.parent_object_id = OBJECT_ID(@table_name) AND fkc.constraint_object_id = OBJECT_ID(@constraint_name)
        ORDER BY fkc.constraint_column_id

        SET @sql = LEFT(@sql, LEN(@sql) - 1) + ') REFERENCES ' + QUOTENAME('centegy_sndpro_uet.' + @referenced_table_name) + '('

        SELECT @sql = @sql + QUOTENAME(c.name) + ','
        FROM sys.foreign_key_columns fkc
        JOIN sys.columns c ON c.object_id = fkc.referenced_object_id AND c.column_id = fkc.referenced_column_id
        WHERE fkc.parent_object_id = OBJECT_ID(@table_name) AND fkc.constraint_object_id = OBJECT_ID(@constraint_name)
        ORDER BY fkc.constraint_column_id

        SET @sql = LEFT(@sql, LEN(@sql) - 1) + ')'
        end
        FETCH NEXT FROM cursor_tables INTO @table_name, @constraint_name, @constraint_type, @referenced_table_name, @referenced_constraint_name
END

CLOSE cursor_tables
DEALLOCATE cursor_tables

原脚本存在的问题 #

• 主键约束名硬编码为PK_@table_name，与原库实际约束名不符，易引发命名冲突
• 外键查询逻辑未准确关联约束对象，可能导致列拼接错误
• 硬编码模式名centegy_sndpro_uet，未适配动态模式场景
• 仅拼接SQL语句但未执行，无法实际创建约束

修正后的解决方案 #

1. 生成主键约束创建语句 #

在原数据库「sndpro copy」中执行以下脚本，生成所有主键约束的创建语句：

USE [sndpro copy]
GO

SELECT 
    'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] 
     ADD CONSTRAINT [' + kc.name + '] PRIMARY KEY (' + 
    STRING_AGG('[' + c.name + ']', ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) + ');' AS CreatePKScript
FROM 
    sys.tables t
JOIN 
    sys.key_constraints kc ON kc.parent_object_id = t.object_id 
    AND kc.type_desc = 'PRIMARY_KEY_CONSTRAINT'
JOIN 
    sys.index_columns ic ON ic.object_id = t.object_id 
    AND ic.index_id = kc.unique_index_id
JOIN 
    sys.columns c ON c.object_id = ic.object_id 
    AND c.column_id = ic.column_id
GROUP BY 
    t.schema_id, t.name, kc.name
ORDER BY 
    t.name

将执行结果中的CreatePKScript列内容复制到新数据库「sndpro」中执行，完成主键约束创建。

2. 生成外键约束创建语句 #

在原数据库「sndpro copy」中执行以下脚本，生成所有外键约束的创建语句：

USE [sndpro copy]
GO

SELECT 
    'ALTER TABLE [' + SCHEMA_NAME(parent_t.schema_id) + '].[' + parent_t.name + '] 
     ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY (' + 
    STRING_AGG('[' + parent_c.name + ']', ', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id) + ')
     REFERENCES [' + SCHEMA_NAME(ref_t.schema_id) + '].[' + ref_t.name + '] (' + 
    STRING_AGG('[' + ref_c.name + ']', ', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id) + ');' AS CreateFKScript
FROM 
    sys.foreign_keys fk
JOIN 
    sys.tables parent_t ON parent_t.object_id = fk.parent_object_id
JOIN 
    sys.tables ref_t ON ref_t.object_id = fk.referenced_object_id
JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN 
    sys.columns parent_c ON parent_c.object_id = fkc.parent_object_id 
    AND parent_c.column_id = fkc.parent_column_id
JOIN 
    sys.columns ref_c ON ref_c.object_id = fkc.referenced_object_id 
    AND ref_c.column_id = fkc.referenced_column_id
GROUP BY 
    parent_t.schema_id, parent_t.name, fk.name, ref_t.schema_id, ref_t.name
ORDER BY 
    parent_t.name

将执行结果中的CreateFKScript列内容复制到新数据库「sndpro」中执行（需先执行所有主键约束语句，再执行外键语句，避免依赖错误）。

注意事项 #

• 确保新库表结构与原库完全一致（列名、数据类型、长度等）
• 若新库模式名与原库不同，需手动替换生成语句中的模式名
• 执行前建议在测试环境验证，避免数据冲突或约束创建失败

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