MySQL error: key specification without a key length
mysql, mysql-error-1170, sql
Solution
The error happens because MySQL can index only the first N chars of a BLOB or `TEXT` column. So The error mainly happens when there is a field/column type of `TEXT` or BLOB or those belong to `TEXT` or `BLOB` types such as `TINYBLOB`, `MEDIUMBLOB`, `LONGBLOB`, `TINYTEXT`, `MEDIUMTEXT`, and `LONGTEXT` that you try to make a primary key or index. With full `BLOB` or `TEXT` without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using `BLOB` or `TEXT` types as an index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support a key length limit on `TEXT` or `BLOB`. `TEXT(88)` simply won’t work.
The error will also pop up 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.
The solution to the problem is to remove the `TEXT` or `BLOB` column from the index or unique constraint or set another field as primary key. If you can't do that, and wanting to place a limit on the `TEXT` or `BLOB` column, try to use `VARCHAR` type and place a limit of length on it. By default, `VARCHAR` is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e `VARCHAR(200)` will limit it to 200 characters long only.
Sometimes, even though you don’t use `TEXT` or `BLOB` related type in your table, the Error 1170 may also appear. It happens in a situation such as when you specify `VARCHAR` column as primary key, but wrongly set its length or characters size. `VARCHAR` can only accepts up to 256 characters, so anything such as `VARCHAR(512)` will force MySQL to auto-convert the `VARCHAR(512)` to a `SMALLTEXT` datatype, which subsequently fails with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for `VARCHAR` field.
Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
Problem
I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error: ``` BLOB/TEXT column 'message_id' used in key specification without a key length ``` how can I fix this? edit: I should also point out this table has a composite primary key with multiple columns.