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.
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));