MariaDB won't create table with large VARCHAR as PRIMARY KEY

Question

I tried to create a table in MariaDB, I wanted it to be VARCHAR(767) and PRIMARY KEY. I used this command but this is not what I want to.

CREATE TABLE main(username VARCHAR(767)  NOT NULL);

This command is executed, but if I add PRIMARY KEY the error will be appeared.

CREATE TABLE main(username VARCHAR(767) NOT NULL PRIMARY KEY);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Answer 1

What version are you using? I've tried your command on my MariaDB (10.1.20-MariaDB), the primary key can be created as varchar 767 long.

MariaDB [test]> CREATE TABLE main(username VARCHAR(767)  NOT NULL);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> drop table main;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE main(username VARCHAR(767) NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.20-MariaDB |
+-----------------+
1 row in set (0.00 sec)

Answer 2

The limitation is in the length of the index key in the database engine

Here's a bit more discussion. What is the index key size limit in MariaDB?

Generally I'd discourage using an index on anything that long, and particularly a primary key. I'd use a surrogate (like an integer).

And, really, a 700 character username is getting a bit unwieldy.

Answer 3

VARCHAR(767) allows 767 characters. If the default character set is utf8, that entails 3*767 bytes.

Older versions have a limit of 767 bytes for an individual column in an index. (And PRIMARY KEY is an index.)

Here are the varchar limits in older versions:

• latin1: 765 characters (+2 bytes for length; each character is 1 byte)
• utf8: 255 characters (utf8 is the 3-byte subset of the 'real' utf8)
• utf8mb4: 191 characters (4-byte utf8 encodings are allowed.

For newer versions, the limits are about 4 times that.

If you really need a PRIMARY KEY or UNIQUE KEY bigger than the available limit, we can discuss tricks using hashes, etc. But we need to understand what type of data it is, what version you are using, what charset you need, etc.