ERROR 1170 (42000): BLOB/TEXT column 'subject' used in..

i had a table which stores some multilingual data of an application. the 'subject' field was a 'varchar' and length was 100.

to facilitate an application modification, i had to convert it to a bit bigger field, of type 'text' with a 1000 character length.

after altering the table, i tried to make it an 'unique key' and got following error.

#1170 - BLOB/TEXT column 'subject' used in key specification without a key length

the reason was, mysql does not allow creation of indexes for blob/text types. but it allows it for 'prefix' of the column. this is how you can do it (here, the prefix length was 50)

ALTER TABLE `translate` ADD UNIQUE (subject(50) ,language_id);