Databases
StackOverflow2013
Postsdbo
POMigrating from MySQL to SQL Server, issues with constraints
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMigrating from MySQL to SQL Server, issues with constraints
    primarykey
    data
    text
    <p>I created a web app that uses a MySQL database, but I have to migrate the database to <strong>Microsoft SQL Server 2008 R2</strong> and I'm using the <strong>SQL Server Migration Assistant</strong> (SSMA).</p> <p>I'm getting errors in my report for some tables that use foreign keys.</p> <h2>1. Self-referencing foreign keys</h2> <p>I have one table that has a parent-child relationship between rows; <strong>map</strong> table:</p> <pre><code>| 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 | </code></pre> <p>The report generated by SQL Server Migration Assistant (SSMA) shows the SQL that would be used to create a table in SQL Server.</p> <p><strong>MySQL</strong> (source):</p> <pre><code>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; </code></pre> <p><strong>SQL Server</strong> (target, SQL generated by SSMA):</p> <pre><code>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 </code></pre> <p>As you can see it gives an error indicating it changed my <code>ON UPDATE CASCADE</code> and <code>ON DELETE CASCADE</code> to <code>NO ACTION</code> in order to "to avoid circular references of cascaded foreign keys."</p> <h2>2. Many-to-many tables</h2> <p>I have two tables that got an error for "multiple paths" and similarly were changed to <code>NO ACTION</code>.</p> <p><strong>asset_property</strong> table:</p> <pre><code>| asset_id | property_id | property_value | |:--------:|:-----------:|:---------------:| | 933 | 1 | Joseph | | 933 | 2 | Green | | 936 | 1 | Jacob | | 936 | 2 | Yellow | | 942 | 1 | Susan | | 942 | 2 | Blue | </code></pre> <p><strong>MySQL</strong> (source):</p> <pre><code>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; </code></pre> <p><strong>SQL Server</strong> (target, SQL generated by SSMA):</p> <pre><code>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 </code></pre> <p>I've only found one <a href="http://blogs.msdn.com/b/ssma/archive/2011/03/19/mysql-to-sql-server-migration-method-for-correcting-schema-issues.aspx" rel="noreferrer">article</a> 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."</p> <p>Thanks for any help!!</p> <hr> <p><img src="https://i.stack.imgur.com/hbQUC.png" alt="db diagram"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjared_flack
    1. USjared_flack
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCircular 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.
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. USAaron Bertrand
    2. CO@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.
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. USjared_flack
    3. COCan'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.
      singulars
      1. POMigrating from MySQL to SQL Server, issues with constraints
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload