mysql - 从 MySQL 迁移到 SQL Server，约束问题

Question

我创建了一个使用 MySQL 数据库的 Web 应用程序，但我必须将数据库迁移到 Microsoft SQL Server 2008 R2 并且我正在使用 SQL Server 迁移助手 (SSMA)。

我的报告中出现了一些使用外键的表的错误。

1。自引用外键

我有一个表，行之间存在父子关系； map 表格:

| map_id | map_title           | latitude  | longitude  | map_zoom | map_parent |
|:------:|:-------------------:|:---------:|:----------:|:--------:|:----------:|
| 1      | My Parent Map       | 50.364829 | -52.635623 | 17       | NULL       |
| 2      | Some Child Map      | 50.366916 | -52.634718 |          | 1          |
| 3      | Another Child Map   | 50.364898 | -52.634543 |          | 1          |
| 4      | My Last Example Map | 50.361986 | -52.638891 |          | 3          |

SQL Server 迁移助手 (SSMA) 生成的报告显示将用于在 SQL Server 中创建表的 SQL。

MySQL(来源):

1  CREATE
2      TABLE `map`
3          (
4              `map_id` int(11) UNSIGNED NOT NULL  AUTO_INCREMENT, 
5              `map_title` varchar(50) DEFAULT NULL, 
6              `latitude` varchar(12) DEFAULT NULL, 
7              `longitude` varchar(12) DEFAULT NULL, 
8              `map_zoom` varchar(5) NOT NULL, 
9              `map_parent` int(11) UNSIGNED DEFAULT NULL, 
10               PRIMARY KEY  (`map_id`) , 
11               KEY `map_parent`  (`map_parent`) , 
12               CONSTRAINT `map_ibfk_2` FOREIGN KEY  (`map_parent`)  REFERENCES `map`  (`map_id`)   ON DELETE CASCADE   ON UPDATE CASCADE 
13          )  ENGINE = InnoDB AUTO_INCREMENT = 12 DEFAULT  CHARSET = utf8;

SQL Server(目标，SSMA 生成的 SQL):

1  CREATE TABLE dbo.map
2  (
3      map_id bigint NOT NULL IDENTITY(12, 1), 
4      map_title nvarchar(50) NULL DEFAULT NULL, 
5      latitude nvarchar(12) NULL DEFAULT NULL, 
6      longitude nvarchar(12) NULL DEFAULT NULL, 
7      map_zoom nvarchar(5) NOT NULL, 
8      map_parent bigint NULL DEFAULT NULL, 
9      CONSTRAINT PK_map_map_id PRIMARY KEY (map_id), 
10      /* 
11      *   SSMA error messages:
12      *   M2SS0040: ON DELETE  CASCADE|SET NULL|SET DEFAULT action  was changed to NO ACTION to avoid circular references of cascaded foreign keys.
13  
14      CONSTRAINT map$map_ibfk_2 FOREIGN KEY (map_parent) REFERENCES dbo.map (map_id) 
15           ON DELETE NO ACTION 
16          /* 
17          *   SSMA error messages:
18          *   M2SS0036: ON UPDATE CASCADE|SET NULL|SET DEFAULT action  was changed to NO ACTION to avoid circular references of cascaded foreign keys.
19  
20           ON UPDATE NO ACTION
21          */
22  
23  
24      */
25  
26  
27  )
28  GO
29  CREATE NONCLUSTERED INDEX map_parent
30      ON dbo.map (map_parent ASC)
31  GO

如您所见，它给出了一个错误，表明它将我的 ON UPDATE CASCADE 和 ON DELETE CASCADE 更改为 NO ACTION 以“避免级联外键的循环引用。”

2。多对多表

我有两个表出现“多路径”错误，并且类似地更改为 NO ACTION。

asset_property 表:

| asset_id | property_id | property_value  |
|:--------:|:-----------:|:---------------:|
| 933      | 1           | Joseph          |
| 933      | 2           | Green           |
| 936      | 1           | Jacob           |
| 936      | 2           | Yellow          |
| 942      | 1           | Susan           |
| 942      | 2           | Blue            |

MySQL(来源):

1  CREATE
2      TABLE `asset_property`
3          (
4              `asset_id` int(11) NOT NULL, 
5              `property_id` int(11) NOT NULL, 
6              `property_value` varchar(100) DEFAULT NULL, 
7               PRIMARY KEY  (`asset_id`, `property_id`) , 
8               KEY `asset_id`  (`asset_id`) , 
9               KEY `property_id`  (`property_id`) , 
10               CONSTRAINT `asset_property_ibfk_1` FOREIGN KEY  (`asset_id`)  REFERENCES `asset`  (`asset_id`)   ON DELETE CASCADE   ON UPDATE CASCADE , 
11               CONSTRAINT `asset_property_ibfk_2` FOREIGN KEY  (`property_id`)  REFERENCES `property`  (`property_id`)   ON DELETE CASCADE   ON UPDATE CASCADE 
12          )  ENGINE = InnoDB DEFAULT  CHARSET = utf8;

SQL Server(目标，SSMA 生成的 SQL):

1  CREATE TABLE dbo.asset_property
2  (
3      asset_id int NOT NULL, 
4      property_id int NOT NULL, 
5      property_value nvarchar(100) NULL DEFAULT NULL, 
6      CONSTRAINT PK_asset_property_asset_id PRIMARY KEY (asset_id, property_id), 
7      /* 
8      *   SSMA error messages:
9      *   M2SS0041: ON DELETE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
10  
11      CONSTRAINT asset_property$asset_property_ibfk_1 FOREIGN KEY (asset_id) REFERENCES dbo.asset (asset_id) 
12           ON DELETE NO ACTION 
13          /* 
14          *   SSMA error messages:
15          *   M2SS0037: ON UPDATE CASCADE|SET NULL|SET DEFAULT action was changed to NO ACTION to avoid multiple paths in cascaded foreign keys.
16  
17           ON UPDATE NO ACTION
18          */
19  
20  
21      */
22  
23  , 
24      CONSTRAINT asset_property$asset_property_ibfk_2 FOREIGN KEY (property_id) REFERENCES dbo.property (property_id) 
25           ON DELETE CASCADE 
26           ON UPDATE CASCADE
27  )
28  GO
29  CREATE NONCLUSTERED INDEX asset_id
30      ON dbo.asset_property (asset_id ASC) 31  GO 32  CREATE NONCLUSTERED INDEX property_id
33      ON dbo.asset_property (property_id ASC) 34  GO

我只找到了一个 article谈论这些错误。文章对自引用表错误的解决方案似乎并不适用，而多对多错误解决方案只是删除约束“因为应用程序或用户不应该修改这些值”。

感谢您的帮助!!

---

Answer 1

我没有使用 SSMA 的经验，我使用 SSIS 迁移数据库。阅读您的问题后，我想我可以给您一些建议..

你已经在你的数据库中创建了循环依赖。当你的数据库有循环依赖并且你在两个依赖表中都有数据时，如果你想迁移数据，你必须禁用目标数据库中的键约束。为了避免第二个问题应该避免级联选项，而是使用存储过程或触发器。

你可以看看这个链接:

http://blogs.msdn.com/b/ssma/archive/2011/03/19/mysql-to-sql-server-migration-method-for-correcting-schema-issues.aspx

我已经测试了链接，它工作正常