I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters is not 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.
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
BLOB types such as
MEDIUMTEXT , and
LONGTEXT that you try to make a primary key or index. With full
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
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(88) simply won’t work.
The error will also pop up when you try to convert a table column from
non-BLOB type such as
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
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
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
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