MySQL: How to alter varchar(255) UNIQUE column to UNIQUE Text NOT NULL?

The current column is a VARCHAR(255) NOT NULL, so how do I change it to TEXT NOT NULL?

NOTE: The column intended to be changed its property type is a UNIQUE KEY combination of another column. E.g.

UNIQUE KEY (Name, Description)

The column description is currently in varchar(255). It can't be changed because of this:

ERROR 1170 (42000): BLOB/TEXT column 'description' used in key specification without a key length

I need it to be TEXT, else I need to recreate the whole thing?? I got some tedious and important data already in it. It's going to be troublesome to recreate.

Answers


Are you going to use TEXT column as part of UNIQUE KEY? It's VERY inefficient! Don't do that! I'm strongly suggest you to:

  • Add additional column named for example 'description_hash' char(32) not null default ''
  • Store the hash-value for description field into it. For ex. description_hash=MD5(description)
  • Change your key to UNIQUE KEY (name, description_hash)

Ofcourse you'll need to keep the description_hash column up-to-date in your code, but as see - in most cases it is require just few code changes. Or you can use trigger for handling this.


I had exactly the same problem.

I added a new char(32) column (I called it hash and added a unique index on it) and two triggers.

delimiter | 

CREATE TRIGGER insert_set_hash
    BEFORE INSERT ON my_table_name
    FOR EACH ROW BEGIN  
          SET NEW.hash = MD5(NEW.my_text);
    END; |

CREATE TRIGGER update_set_hash
    BEFORE UPDATE ON my_table_name
    FOR EACH ROW BEGIN  
           SET NEW.hash = MD5(NEW.my_text);
    END; |


delimiter ;

By using the triggers and adding a UNIQUE index on hash you can be sure that the hash values will always be up-to-date and unique.


alter table your_table
modify column your_column text not null;

for unique key

alter table your_table 
add unique index your_index_name (your_column(your_length));

your_length = allow up 1000 bytes

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used


Both length (description + another column can not longer than 1000),so

alter table your_table 
add unique index your_index_name (description(800), another_column(200));

Need Your Help

Css is added although html element does not have a certain class

html css less

I have a CSS (the style is written in LESS) problem regarding this style:

Shatter an element, sending pieces in random directions

javascript jquery html css jquery-ui

I'm attempting to "shatter" an element - say, for example, an image - and send pieces of it flying in random directions. When the pieces reach their destination (i.e. x distance in pixels), they be...