CODEWITHSUNDEEP
mysqlsqlmysql-error-1170
George Leow
George Leow

Reputation: 2373

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.

Upvotes: 5

Views: 18027

Answers (3)

Claude
Claude

Reputation: 879

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.

Upvotes: 4

Vadym
Vadym

Reputation: 5284

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.

Upvotes: 12

ajreal
ajreal

Reputation: 47321

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

Upvotes: 2

Related Questions