6

So I'm trying to create my table as follows:

CREATE TABLE company
  (
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name TEXT UNIQUE NOT NULL,

    INDEX(name(20))
  );

It's giving me this error: 

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

I'm not sure why it's not working as I'm following the guide here: https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html

A. L
  • 11,695
  • 23
  • 85
  • 163

2 Answers2

6

You're focusing on the wrong line.

name TEXT UNIQUE NOT NULL,

Indexes on BLOB and TEXT columns must be prefix indexes, therefore, it's not possible to impose a UNIQUE constraint on a TEXT column. You also can't make such a column part of the primary key or of a foreign key constraint.

Two common solutions:

  1. Don't use an TEXT column, use VARCHAR.

  2. If you really need a long column to be unique, create a second column of type CHAR, COLLATE ascii_bin, add a unique constraint to it, and size it appropriately for the base64 representation of a chosen cryptographic hash (md5, sha). Use BEFORE INSERT and BEFORE UPDATE triggers to force this column to contain the hash of the long column, thus indirectly enforcing uniqueness. Data type CHAR because all hashes are the same length, and ascii_bin because this is the most appropriate collation for base64. Why base64? It's a tradeoff of storage space for readability, using 24 characters to store an md5 hash, which is about halfway between binary (16 characters for md5, efficient) and hex (32 characters for md5, inefficient) encoding in terms of storage space.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • How well does VARCHAR support international languages? – A. L Aug 12 '17 at 09:05
  • As far as I know, there is no difference. The character set and collation of the column determine this, not the data type. If you were using the default `latin1` then international support would be almost nonexistent. See instead [`utf8mb4`](https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html). – Michael - sqlbot Aug 12 '17 at 15:07
  • For text/blob I read somewhere that you could make the first x characters as the index, which is why I had `INDEX(name(20))`. My link even demonstrates this. – A. L Aug 13 '17 at 03:42
  • 4
    @A.Lau You are right. That index will have a prefix. But the `unique` needs to creates *another* index - without prefix, which is not supported for blobs. That is what Michael meant with "You're focusing on the wrong line". The line `INDEX(name(20))` is perfectly fine and your table would be created if you'd remove the `unique`. – Solarflare Aug 13 '17 at 15:03
  • @Solarflare Ah thanks, I guess I'll switch it over to VARCHAR then. – A. L Aug 13 '17 at 23:20
  • Somewhat related, if I wanted to create a unique of several columns e.g. `UNIQUE (name, other_name)` do they all need to be VARCHAR or some other specified length as well? Or can they be BLOB/TEXT this time? – A. L Aug 13 '17 at 23:23
  • They all need to be `VARCHAR` unless you want to use a modified version of the trigger + crypto hash technique. – Michael - sqlbot Aug 13 '17 at 23:26
  • 2
    Be aware that you cannot index (`INDEX` or `UNIQUE`) a `VARCHAR` bigger than a certain amount. (The size varies with version of MySQL and `CHARACTER SET` -- between 191 and 3072.) – Rick James Aug 14 '17 at 12:31
  • @RickJames Yeah, I was hoping to get 255, but I fiddled around and just settled with 150 :( – A. L Aug 20 '17 at 12:28
0

I have found the error. You didn't specify the length of attribute name. Use varchar(length) instead of TEXT, where length is a number.

So your code would be if you want the name of maximum 50 letters :

CREATE TABLE company ( id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(50) UNIQUE NOT NULL,
INDEX(name(20))
);

You can give any value in varchar() but it should be integer.