MySQL Error: BLOB/TEXT Column Used in Key Specification

[Balgovind]

In MySQL database, when creating a new table or altering an existing table with primary keys, unique constraints and indexes, I receive the following error message -

"ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length"

Any ideas....

[Praetor]

The above error occurs because MySQL is able to index only the first N chars of a BLOB or TEXT column. Hence, this occurs when there is a field/column type of TEXT or BLOB or those belonging to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index.

however, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size with full BLOB or TEXT without the length value. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length.

[Katty]

In MySQL, you need to give a key size ( 256 and above ) to the columns of TEXT and BLOB type.

> ALTER TABLE mytable ADD INDEX mytable_username_index (username(256));

[kelfro]

To solve this problem,
remove the TEXT or BLOB column from the index or unique constraint, or set another field as primary key.

If you want to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it.
Note - VARCHAR is limited to a maximum of 255 characters by default, but you can specify its limit within a bracket right after its declaration for e.g. VARCHAR(200) - This will limit it to 200 characters long only.

[Zecho]

The error may also occur while defining a new index with Alter Table manipulation statement. When you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.