Why is this create table sql query giving me the error "#1071 - Specified key was too long; max key length is 767 bytes"

Question

CREATE TABLE IF NOT EXISTS members_table (
     `id` int(10) NOT NULL AUTO_INCREMENT,
     `customerNumber` varchar(10) DEFAULT NULL,
     `firstName` varchar(64) NOT NULL,
     `lastName` varchar(64) NOT NULL,
     `address` varchar(64) DEFAULT NULL,
     `city` varchar(32) DEFAULT NULL,
     `state` varchar(2) DEFAULT NULL,
     `country` varchar(2) DEFAULT NULL,
     `zipCode` varchar(5) NOT NULL,
     `phoneNumber` varchar(12) DEFAULT NULL,
     `emailAddress` varchar(200) NOT NULL,
     `dateOfBirth` varchar(10) DEFAULT NULL,
     `subscribe` tinyint(1) DEFAULT NULL,
     `password` varchar(34) DEFAULT NULL,
     `resetKey` varchar(34) DEFAULT NULL,
     `joinDate` datetime  NOT NULL,
     `active` tinyint(1) DEFAULT NULL,
     `deleted` tinyint(1) DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `emailAddress` (`emailAddress`)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Answer 1

According to this question:

MySQL assumes 3 bytes per utf8 character. 255 characters is the maximum index size you can specify per column, because 256x3=768, which breaks the 767 byte limit.

Since you specified utf8mb4 collation, MySQL assumes 4 bytes per character. From MySQL docs:

The utf8mb4 character set uses a maximum of four bytes per character.

Your emailAddress field is 200 characters long at maximum thus the key length would be 800 bytes while error explicitly says the limit is 767 bytes.

Answer 2

MySQL assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.

Use utf8 instead of utf8mb4

Try this

CREATE TABLE IF NOT EXISTS members_table (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `customerNumber` varchar(10) DEFAULT NULL,
 `firstName` varchar(64) NOT NULL,
 `lastName` varchar(64) NOT NULL,
 `address` varchar(64) DEFAULT NULL,
 `city` varchar(32) DEFAULT NULL,
 `state` varchar(2) DEFAULT NULL,
 `country` varchar(2) DEFAULT NULL,
 `zipCode` varchar(5) NOT NULL,
 `phoneNumber` varchar(12) DEFAULT NULL,
 `emailAddress` varchar(200) NOT NULL,
 `dateOfBirth` varchar(10) DEFAULT NULL,
 `subscribe` tinyint(1) DEFAULT NULL,
 `password` varchar(34) DEFAULT NULL,
 `resetKey` varchar(34) DEFAULT NULL,
 `joinDate` datetime  NOT NULL,
 `active` tinyint(1) DEFAULT NULL,
 `deleted` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `emailAddress` (`emailAddress`)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;