Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 23-05-2009
Member
 
Join Date: Feb 2009
Posts: 47
MySQL Error: BLOB/TEXT Column Used in Key Specification

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....
Reply With Quote
  #2  
Old 23-05-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: MySQL Error: BLOB/TEXT Column Used in Key Specification

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.
Reply With Quote
  #3  
Old 23-05-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: MySQL Error: BLOB/TEXT Column Used in Key Specification

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));
Reply With Quote
  #4  
Old 23-05-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: MySQL Error: BLOB/TEXT Column Used in Key Specification

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.
Reply With Quote
  #5  
Old 23-05-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: MySQL Error: BLOB/TEXT Column Used in Key Specification

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "MySQL Error: BLOB/TEXT Column Used in Key Specification"
Thread Thread Starter Forum Replies Last Post
Mysql error message "Unknown column in on clause" Carnie Software Development 3 21-11-2009 11:03 AM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 09:36 PM
Column Formatting for Text Box in Microsoft Publisher gmatting Windows Software 1 07-09-2009 09:19 AM
How to count number of text occurences in Excel column Anas Windows Software 3 09-06-2009 05:01 PM
mysql update column with another column Gunter Software Development 3 23-05-2009 09:44 AM


All times are GMT +5.5. The time now is 08:57 AM.