-1
CREATE TABLE IF NOT EXISTS `dbo`.`Discounts` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Description` LONGTEXT CHARACTER SET 'utf8mb4' NULL,
  `Code` VARCHAR(255) CHARACTER SET 'utf8mb4' NULL,
  `Catalog_Id` INT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE INDEX `UQ_Discounts_CatalogCode` (`Catalog_Id` ASC, `Code`(255) ASC),
  CONSTRAINT `Discount_Catalog`
    FOREIGN KEY (`Catalog_Id`)
    REFERENCES `dbo`.`Catalogs` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

I was going to migrate data from SSMS to mysql. At last I got this error. How can I fix it?

alroc
  • 27,574
  • 6
  • 51
  • 97
Sheran
  • 11
  • 3
  • Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) – HoneyBadger Sep 06 '17 at 11:42
  • You can be smart about this and make the hash of the textual value unique, not the actual value. It saves a ton of space. – Mjh Sep 06 '17 at 11:52

2 Answers2

0

You're using utf8mb4 so 4 bytes per character in needed in the worst case

Maximum key length is 767 bytes that is 255 characters for utf8 and 191 characters for utf8mb4

multiple options:

  • use a different charset
  • reduce the number of characters required

  • build the uniqueness constraint on a shorter prefix, like this:

    (UNIQUE INDEX 'UQ_Discounts_CatalogCode' ('Catalog_Id' ASC, 'Code'(191) ASC))

Oleg Kuralenko
  • 11,003
  • 1
  • 30
  • 40
0

You can avoid that error by using the Barracuda file format and enabling the innodb_large_prefix variable.

See my blog post for full details: http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

Ike Walker
  • 64,401
  • 14
  • 110
  • 109