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

Question

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.

can easily get the answer by googling

Shakti Singh
– Shakti Singh

2011-01-20 07:53:10 +00:00
Commented Jan 20, 2011 at 7:53 — Shakti Singh
– Shakti Singh, Commented Jan 20, 2011 at 7:53
Please reread the question. Sorry..

George Leow
– George Leow

2011-01-20 08:06:01 +00:00
Commented Jan 20, 2011 at 8:06 — George Leow
– George Leow, Commented Jan 20, 2011 at 8:06

Vadym · Accepted Answer · 2011-01-20 08:36:34Z

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.

Hi yeah I was trying to make text the unique key, now I remain varchar as unique. I created another field called More_About. Not sure if this is the best way but I read there's no way to use text as UNIQUE. Besides, I can't even find a way to alter a UNIQUE key column's property yet. The examples are vague. I will re-look at the answers here later, thanks.
You dont need to use full text as unique index - use just a hash (md5, sha1, fnv, etc) from this text as unique index, as i described above.

Claude · Accepted Answer · 2012-06-13 22:29:40Z

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.

ajreal · Accepted Answer · 2011-01-20 08:23:42Z

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

Hey sorry I am updating this in my question. The question is supposed to be that column is also a UNIQUE KEY.
I don't understand, can you write it out if I tell you the column's name is "description". It's a combination of another column called "name" that both forms a UNIQUE KEY. How to write it in MySQL query? I only need to change description property to TEXT NOT NULL. Thanks..
alter table table_name add unique index description (description(1000)); // Hi could you clarify this? it's not working. Besides, your code doesn't specify alter which column??

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

3 Comments

Linked

Hot Network Questions