Mysql / mariadb - create table column index not working

Question

So, I am trying to create a table as follows:

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

This gives me this error:

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

I am not sure why it does not work, as I follow the manual here: https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

Answer 1

You are focusing on the wrong line.

 name TEXT UNIQUE NOT NULL, 

Indexes in BLOB and TEXT columns must be prefix indexes, so it is not possible to impose a UNIQUE on a TEXT column. You also cannot make such a part of the primary key column or foreign key constraints.

Two common solutions:

• Do not use the TEXT column, use VARCHAR .

• If you really need a long column that is unique, create a second column of type CHAR , COLLATE ascii_bin , add a unique constraint to it, and configure it accordingly to represent base64 the selected cryptography hash (md5, sha). Use the BEFORE INSERT and BEFORE UPDATE triggers to force this column to hash the long column, thereby indirectly applying uniqueness. The data type is CHAR because all hashes are the same length and ascii_bin because this is the most suitable base64 mapping. Why base64? This is a compromise of reading space for reading, using 24 characters to store the md5 hash, which is about halfway between binary (16 characters for md5, efficient) and hexadecimal (32 characters for md5, inefficient) encoding in terms of storage space.

Answer 2

I found a mistake. You did not specify the length of the attribute name . Use varchar ( length ) instead of TEXT , where length is a number.

Thus, your code will be if you want the name to not exceed 50 letters:

Company CREATE TABLE (id INT UNCERTAIN PRIMARY KEY NOT ZERO AUTO_INCRIEMENT,
name varchar (50) UNIQUE NOT NULL,
INDEX (name (20))
);

You can specify any value in varchar () , but it must be an integer.