Asked  7 Months ago    Answers:  5   Viewed   581 times

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.

 Answers

48

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

Tuesday, June 1, 2021
 
Hexaholic
answered 7 Months ago
72

Sounds like you have Schroedinger's table...

Seriously now, you probably have a broken table. Try:

  • DROP TABLE IF EXISTS contenttype
  • REPAIR TABLE contenttype
  • If you have sufficient permissions, delete the data files (in /mysql/data/db_name)
Wednesday, June 2, 2021
 
HamidR
answered 7 Months ago
28

I believe desc is reserved. It is used in an ORDER BY clause

You may be able to get away with using it if you put back-ticks around it, but I think you would be better off changing the name to a non-reserved word.

Saturday, July 31, 2021
 
davidb
answered 4 Months ago
19

There where some typo errors, like timestamp is a key word, you had an extra comma after event char(255),.

Try this:

    CREATE TABLE pulseLog (
    id INT(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `timeStamp` TIMESTAMP,
    `currentTime` DATETIME,
    `timeDiff` INT(10),
    `unixTime` INT(10),
    `currentR1` FLOAT,
    `currentS2` FLOAT,
    `currentT3` FLOAT,
    `currentAverageR1` FLOAT,
    `currentAverageS2` FLOAT,
    `currentAverageT3` FLOAT,
    `temp0` FLOAT,
    `temp1` FLOAT,
    `temp2` FLOAT,
    `temp3` FLOAT,
    `temp4` FLOAT,
    `temp5` FLOAT,
    `pulses` INT,
    `event` char(255)
 ) CHARACTER SET UTF8;

Here is the SQL Fiddle DEMO

Edit:

Apart from that your syntax for timestamp was not supported. For reference on date, datetime and timestamp check here

Friday, August 20, 2021
 
joostvandriel
answered 4 Months ago
42

Yes, you can do this (and you should, from a database design point of view).

However, consider what it means if user_id is the primary key on table 2. You are in effect saying that each row in table 2 corresponds to a user, but you already have a table where each row corresponds to a user: table 1. This raises the question "why then don't you put all data of table 2 into nullable columns in table 1?". After all, having two tables means you will have to make two queries to get this data instead of one.

Now there are some scenarios where this practice might be a good idea:

  • if you have lots of users but only a few rows in table 2, perhaps the query on table 2 will be only performed rarely; at the same time, you gain storage space and modification speed on table 1
  • it might be possible in the future for the primary key of table 2 to change, while the foreign key remains; if you put all the data in table 1, this modification would most likely break your database model

It can be a good idea, but it depends on the particulars of your application.

Monday, September 13, 2021
 
Pascal Elzenaar
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share