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.

Phene

can easily get the answer by googling – Aegospotami

Please reread the question. Sorry.. – Phene

Answer 1

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.

Reprography

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. – Phene

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. – Reprography

Answer 2

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.

Jollanta

Answer 3

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

Millda

Hey sorry I am updating this in my question. The question is supposed to be that column is also a UNIQUE KEY. – Phene

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.. – Phene

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?? – Phene