The duplicate key value is (<NULL>, <NULL>)-CodePudding

So I'm trying to migrate a table from MySQL to MSSQL (sql server migration assistant MySQL), but I get this error:

Migrating data...
Analyzing metadata...
Preparing table testreportingdebug.testcase...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`testreportingdebug`.`testcase`': > [SwMetrics].[testreportingdebug].[testcase], 8855 rows total
Violation of UNIQUE KEY constraint 'testcase$Unique'. Cannot insert duplicate key in object 'testreportingdebug.testcase'. The duplicate key value is (<NULL>, <NULL>).
 Errors: Violation of UNIQUE KEY constraint 'testcase$Unique'. Cannot insert duplicate key in object 'testreportingdebug.testcase'. The duplicate key value is (<NULL>, <NULL>).
Completing migration of table `testreportingdebug`.`testcase`...
Migration complete for table '`testreportingdebug`.`testcase`': > [SwMetrics].[testreportingdebug].[testcase], 0 rows migrated (Elapsed Time = 00:00:00:01:352).
Data migration operation has finished.
    0 table(s) successfully migrated.
    0 table(s) partially migrated.
    1 table(s) failed to migrate.

I've just copied three rows from my table, and this is what they look like:

'1',   'Pump# TimeToService',           NULL, NULL, 'A general test case comment ...', '0'
'2',   'Config.SlaveMinimumReplyDelay', NULL, NULL, NULL,                              '0'
'3',   'Config.RESERVED',               NULL, NULL, NULL,                              '0'

If you are wondering how the colons in the MySQL table is setup, here you go:

Is is because right, left and comment can be null?

DDL of table

CREATE TABLE `testcase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `TestCaseName` varchar(150) DEFAULT NULL,
  `Left` int(11) DEFAULT NULL,
  `Right` int(11) DEFAULT NULL,
  `Comment` text,
  `Hidden` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique` (`Left`,`Right`)
) ENGINE=InnoDB AUTO_INCREMENT=10580 DEFAULT CHARSET=utf8

CodePudding user response：

Had to remove the Unique part, since their are only NULL.

ALTER TABLE `testreportingdebug`.`testcase` 
DROP INDEX `Unique`;

CodePudding user response：

If you want the strict equivalent in SQL Server of your MySQL table you must create it like this :

CREATE TABLE testcase (
  id int NOT NULL IDENTITY PRIMARY KEY,
  TestCaseName varchar(150),
  [Left] int,
  [Right] int,
  Comment VARCHAR(max),
  [Hidden] tinyint DEFAULT 0,
);
CREATE UNIQUE INDEX X_testcase_right_left 
   ON testcase ([Left], [Right])
   WHERE [Left] IS NOT NULL
     AND [Right] IS NOT NULL;

By the way, column names "Right", "left", "hidden" are SQL / MS SQL Server reserved words and should not be used at anytime for SQL identifiers (table name, colum name, proc name...)

The complete list can be obtain here