Migrating from MySQL to SQL Server, issues with constraints

Question

I created a web app that uses a MySQL database, but I have to migrate the database to Microsoft SQL Server 2008 R2 and I'm using the SQL Server Migration Assistant (SSMA).

I'm getting errors in my report for some tables that use foreign keys.

1. Self-referencing foreign keys

I have one table that has a parent-child relationship between rows; map table:

| 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          |

The report generated by SQL Server Migration Assistant (SSMA) shows the SQL that would be used to create a table in SQL Server.

MySQL (source):

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 (target, SQL generated by SSMA):

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

As you can see it gives an error indicating it changed my ON UPDATE CASCADE and ON DELETE CASCADE to NO ACTION in order to "to avoid circular references of cascaded foreign keys."

2. Many-to-many tables

I have two tables that got an error for "multiple paths" and similarly were changed to NO ACTION.

asset_property table:

| 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 (source):

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 (target, SQL generated by SSMA):

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

I've only found one article that talks about these errors. The article's solution for the self-referencing table error doesn't seem to apply, and the many-to-many error solution is to just remove the constraint "because the application or user shouldn’t be modifying these values."

Thanks for any help!!

db diagram

Circular references are considered bad. Since you can create a map_id of 1, a map_id of 2 with a parent_id of 1, and then update 1 to set its parent_id to 2, the schema simply prevents cascading actions in that case. There is no fix for this. As for the second error, does another table reference `dbo.asset_property` with cascading actions as well? Or does `asset_id` reference something else? There is obviously another `asset_id` somewhere else in your schema or you'd get the exact same error for `property_id`. FWIW, I never use the cascading options in SQL Server. — Aaron Bertrand, May 08 '12 at 21:02
@AaronBertrand I'm beginning to see that you wouldn't need to `ON UPDATE CASCADE` since the IDs, or "surrogate keys", won't change. But when a user deletes a map, I want all the assets that belong to that map to also be deleted. — jared_flack, May 09 '12 at 12:35
Can't you control that from a stored procedure? Are you really letting users issue ad hoc delete from table? A trigger can also take the place of cascade. I'd rather have more control over the consequences. — Aaron Bertrand, May 09 '12 at 13:27

Abdur Rahman · Accepted Answer · 2013-04-28T10:57:34.310

I am not experienced with SSMA, I have used SSIS for migrating databases.After reading your question, I think I could give you some suggessions..

You have created circular dependency in your database.When your database have circular dependencies and you have data in both dependent tables, if you want migrate the data you must have to disable the key constraints in the destination database.To avoid the second problem should avoid the cascading option and instead use stored procedure or trigger.

You can take a look of this link :

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

I've tested the link and it was working fine

Migrating from MySQL to SQL Server, issues with constraints

1. Self-referencing foreign keys

2. Many-to-many tables

1 Answers1