mysql/mariadb - create table column index not working

Question

So I'm trying to create my table as follows:

CREATE TABLE company
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name TEXT UNIQUE NOT NULL,

    INDEX(name(20))
  );

It's giving me this error:

ERROR 1170 (42000): BLOB/TEXT column 'name' used in key specification without a key length

I'm not sure why it's not working as I'm following the guide here: https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

Answer 1

You're focusing on the wrong line.

name TEXT UNIQUE NOT NULL,

Indexes on BLOB and TEXT columns must be prefix indexes, therefore, it's not possible to impose a UNIQUE constraint on a TEXT column. You also can't make such a column part of the primary key or of a foreign key constraint.

Two common solutions:

1. Don't use an TEXT column, use VARCHAR.

2. If you really need a long column to be unique, create a second column of type CHAR, COLLATE ascii_bin, add a unique constraint to it, and size it appropriately for the base64 representation of a chosen cryptographic hash (md5, sha). Use BEFORE INSERT and BEFORE UPDATE triggers to force this column to contain the hash of the long column, thus indirectly enforcing uniqueness. Data type CHAR because all hashes are the same length, and ascii_bin because this is the most appropriate collation for base64. Why base64? It's a tradeoff of storage space for readability, using 24 characters to store an md5 hash, which is about halfway between binary (16 characters for md5, efficient) and hex (32 characters for md5, inefficient) encoding in terms of storage space.