My SQL Script erro 1071 - Does Not Work ADD UNIQUE or Change ENGINE

0
votes
  `CREATE TABLE IF NOT EXISTS `Odin`.`Usuario` (
  `CodUsuario` INT NOT NULL AUTO_INCREMENT,
  `CodTipoUsuario` INT NOT NULL,
  `CodConsultorTecnico` INT NULL,
  `CodProdutor` INT NULL,
  `Login` VARCHAR(255) CHARACTER SET 'utf8mb4' NOT NULL,
  `Senha` VARCHAR(32) CHARACTER SET 'utf8mb4' NOT NULL,
  `Ativo` TINYINT(1) NOT NULL,
  `MaiorDataRegistrada` DATETIME NOT NULL,
  `DataLimite` DATETIME NOT NULL DEFAULT '01/01/2014',
  `MaxFazendas` INT NOT NULL DEFAULT 0,
  `MaxFemeas` INT NOT NULL DEFAULT 0,
  `CodUserWeb` INT NOT NULL DEFAULT 0,
  `CodEstudante` INT NULL,
  `UltimoBackup` DATETIME NULL,
  PRIMARY KEY (`CodUsuario`),
  UNIQUE INDEX `UQ__Usuario__00000000000006D8` (`Login` ASC),
  CONSTRAINT `FK_Usu_ref_Est`
    FOREIGN KEY (`CodEstudante`)
    REFERENCES `Odin`.`Estudante` (`CodEstudante`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Fk_Usuario_Ref_Consultor`
    FOREIGN KEY (`CodConsultorTecnico`)
    REFERENCES `Odin`.`ConsultorTecnico` (`CodConsultorTecnico`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Fk_Usuario_Ref_Produtor`
    FOREIGN KEY (`CodProdutor`)
    REFERENCES `Odin`.`Produtor` (`CodProdutor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Fk_Usuario_Ref_TipoUsuario`
    FOREIGN KEY (`CodTipoUsuario`)
    REFERENCES `Odin`.`ConfTipoUsuario` (`CodTipoUsuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) `

I've tried solutions such as: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; or ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf8_unicode_ci;

or ADD UNIQUE

and still continue with the error:

1071 - Specified key was too long; max key length is 767 bytes

Can someone help me? Thank you

1
mysqlmysql-error-1071
Possible duplicate of #1071 - Specified key was too long; max key length is 767 bytes – Daniel W.
I saw this solution, but could not make it work, anyway thanks for trying – Gustavo Melo

1 Answers

1
votes

You are using utf8mb4 which is 1-4 bytes per character.

You chose VARCHAR(255) - 255 characters * 4 bytes = 1020.

Try with less characters. Like VARCHAR(191).

Also have a look into innodb_large_prefix.