Миграция таблиц MSSQL MySQL

На MSSQL:

CREATE TABLE "Document" (
  "ID" INT NOT NULL,
  "IDEDO" NVARCHAR(128) NULL DEFAULT NULL,
  "SigOne" BIT NOT NULL,
  "SigTwo" BIT NOT NULL,
  "KeyOne" NVARCHAR(50) NULL DEFAULT NULL,
  "KeyTwo" NVARCHAR(50) NULL DEFAULT NULL,
  "NameFile" NVARCHAR(128) NOT NULL,
  "FileBody" IMAGE NULL DEFAULT NULL,
  "IsError" BIT NOT NULL DEFAULT b'0',
  PRIMARY KEY ("ID")
);

Вариант на MуSQL:

CREATE TABLE `Document` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `IDEDO` VARCHAR(128) DEFAULT NULL,
  `SigOne` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `SigTwo` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  `KeyOne` VARCHAR(50) DEFAULT NULL,
  `KeyTwo` VARCHAR(50) DEFAULT NULL,
  `NameFile` VARCHAR(128) NOT NULL DEFAULT '',
  `FileBody` LONGBLOB,
  `IsError` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB;

Соответствие некоторых колонок в схемах MSSQL и MySQL:

На MSSQL:

CREATE TABLE "Field" (
  "ID" INT NOT NULL,
  "InnerName" NVARCHAR(50) NOT NULL,
  "DisplayName" NVARCHAR(50) NOT NULL,
  "FieldType" NVARCHAR(50) NOT NULL,
  PRIMARY KEY ("ID")
);

На MySQL:

CREATE TABLE `Field` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `InnerName` CHAR(50) NOT NULL DEFAULT '',
  `DisplayName` CHAR(50) NOT NULL DEFAULT '',
  `FieldType` CHAR(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `InnerName` (`InnerName`)
) ENGINE=INNODB;

Был добавлен уникальный ключ InnerName как доработка схемы БД.

На MSSQL:

CREATE TABLE "FieldMapping" (
  "ID" INT NOT NULL,
  "DocumentID" INT NOT NULL,
  "FieldID" INT NOT NULL,
  "FieldValue" NVARCHAR(100) NOT NULL,
  PRIMARY KEY ("ID")
);

На MySQL:

CREATE TABLE `FieldMapping` (
  `DocumentID` INT UNSIGNED NOT NULL,
  `FieldID` INT UNSIGNED NOT NULL,
  `FieldValue` VARCHAR(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`DocumentID`, `FieldID`)
);

Колонка ID удалена так как не имеет значения, первичный ключ - по двум колонкам DocumentID, FieldID