azure - 从Azure SQL导出数据后如何添加主键和外键约束

Question

我正在使用 SQL Server Management Studio 19 将数据从源数据库迁移到目标数据库。
我选择 SQL Server Native Client 11.0 作为数据源。

对于目标，我还使用“SQL Server Native Client 11.0”并选择目标数据库作为目标。

数据已成功导出，但不存在主键和外键约束。我错过了什么？

如有任何帮助或建议，我们将不胜感激。非常感谢!

Answer 1

有两种方法导出 PK 和 FK。

1. 使用 SSMS 生成 sql 脚本。我们只需要选择表。它将在您的本地 PC 中生成 script.sql。

我们还可以编写一些脚本来手动导出User表的PK和FK。
我创建了一个 sql 脚本来从系统表和 View 中导出 PK 和 FK。

2.1 我们可以使用下面的脚本导出PK。

select  case when colNo = 1 then concat('alter table ',concat(concat(res.schemaName,'.'),res.tableName)) else '' end headerOne,
        case when colNo = 1 then concat(concat('add constraint ' , res.PKName),' primary key( ') else '' end headerTwo,
        case when colNo = 1 then colName else concat(',',colName) end headerThree,
        case when colNo = s2.maxRow then ');' else '' end as headerFour
from (
    select s.name as schemaName,i.name as PKName,ov.name as tableName,c.name as colName,k.colid as colNo,k.keyno as indexNO 
        from
            sysindexes i  
            join sysindexkeys k on i.id = k.id and i.indid = k.indid  
            join sysobjects o on i.id = o.id  
            join sys.objects ov on o.id = ov.object_id
            join sys.schemas s ON ov.schema_id = s.schema_id
            join syscolumns c on i.id=c.id and k.colid = c.colid  
        where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)  
        ) res
left join 
    (select schemaName,PKName,tableName,max(rono) as maxRow
         from 
         (
            select s.name as schemaName,i.name as PKName,ov.name as tableName,c.name as colName, ROW_NUMBER() OVER (PARTITION BY s.name,i.name,ov.name  ORDER BY o.name,k.colid) AS rono
                from
                    sysindexes i  
                    join sysindexkeys k on i.id = k.id and i.indid = k.indid  
                    join sysobjects o on i.id = o.id  
                    join sys.objects ov on o.id = ov.object_id
                    join sys.schemas s ON ov.schema_id = s.schema_id
                    join syscolumns c on i.id=c.id and k.colid = c.colid  
                where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)  
 
         ) s1
         group by schemaName,PKName,tableName 
    ) s2 on res.schemaName = s2.schemaName and res.PKName=s2.PKName and res.tableName=s2.tableName

2.2 然后我们可以从SSMS复制脚本。

2.3 然后我们将脚本粘贴到Staging数据库的查询窗口中来执行脚本。

2.4 创建PK后，用同样的方法，我们可以导出FK并创建它们。

select
   concat(concat('alter table ',c.CONSTRAINT_SCHEMA),concat('.',fk.TABLE_NAME)),
   concat(' add constraint  ', c.CONSTRAINT_NAME),  --cu.COLUMN_NAME
   concat(' foreign key( ',cu.COLUMN_NAME),
   concat(concat(') references ',c.CONSTRAINT_SCHEMA),concat('.',pk.TABLE_NAME)),
   concat(concat('(',pt.COLUMN_NAME),');')
from
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
    on c.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
    on c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    on c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
inner join  (
            select
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            from
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                on i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            where
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    on pt.TABLE_NAME = pk.TABLE_NAME