[fix]-MySQL error key specification without a key length
A table has a primary key field with varchar(255) data type. Sometimes a length of 255 characters is not enough. So the field type is updated as text. During update the below error occurs:
BLOB/TEXT column ‘column_id' used in key specification without a key length
MySQL error: key specification without a key length
Reason for this issue:
1. MySQL will be able to index only the first N chars of a BLOB or TEXT column.
2. The error occurs when one of the below column types is assigned to a primary key column.
3. In BLOB or TEXT type, there is no length specification. MySQL cannot guarantee the uniqueness of the column due to its dynamic size.
Use an integer auto_increment surrogate key column as primary key and the text column with UNIQUE constraint.
1. Create the TEXT field without the unique constraint.
2. Add a sibling VARCHAR field that is unique and contains a MD5 or SHA1 encrypted value of the TEXT field.
3. Calculate and store the encrypted value and check its TEXT field.
To use index in TEXT field, utilize the MyISAM storage engine and the choose data type as FULLTEXT for the index column.
The solution is to specify the index length.
ALTER TABLE [tale_name] ADD INDEX (content(255));
alter table wikitechy_table ADD UNIQUE(emp_name(767), emp_address(767));
NOTE: 767 is the number of characters’ limit up to which MySQL will index columns while dealing with BLOB/TEXT indexes.
Fixes are applicable to the following versions of MySql:
Related Error Tags:
- BLOB/TEXT column ‘value’ used in key specification without a key length
- MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
- DatabaseError: (1170, "BLOB/TEXT column 'tree_path' used in key specification without a key length")