35

i want to make a table in MySQL server with mediumtext column as UNIQUE KEY

CREATE TABLE `parts` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` mediumtext NOT NULL,
      `display_status` int(11) NOT NULL,
       UNIQUE KEY `name` (`name`),
       PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

but this made an error

    BLOB/TEXT column 'name' used in key specification without a key length

when I change the type of `name` to varchar .. it works!

can you tell if i can to make text column as UNIQUE KEY

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
d-doctor
  • 403
  • 1
  • 4
  • 9
  • 1
    possible duplicate : http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length – Tarek Dec 25 '12 at 19:22
  • 1
    thanks @Tarek but that solution force me to use varchar .. and i want to use a type allow me to insert strings longer than 255 characters – d-doctor Dec 25 '12 at 19:29
  • You might add a length, like `UNIQUE KEY `name` (`name`(100)),` if the first 100 were unique. – Joop Eggen Dec 25 '12 at 19:43

3 Answers3

37

Basically you can not use Text column as UNIQUE key. Because practically such a big column will not be unique and there might be a chance of more duplicates. So go for hashing method and use that output as a UNIQUE constraint.

Bapi
  • 309
  • 1
  • 16
Mariappan Subramanian
  • 9,527
  • 8
  • 32
  • 33
  • 8
    Note that technically this is not a solution, because two different text values may generate the same hash - ie a [hash collision](http://en.wikipedia.org/wiki/Collision_(computer_science). To be *sure* of keeping the values unique you would have to store the whole string too and *not* have a unique index on the hash column. – Bohemian Dec 26 '12 at 18:28
  • Here is a more deep answer for who want to really understand why mysql behave like this : https://stackoverflow.com/a/1827099/5677103 – Paul Laffitte Aug 31 '17 at 13:20
  • @Bohemian But practically speaking, the chances that this collision would occur given that we use a decent hashing algorithm would be very very low, so it would be a good pragmatic solution wouldn't it? – hwkd Jan 03 '19 at 04:20
  • @Javascriptonian it depends on the population size and the hash algorithm. For example, when using a 32-bit hash result, at around 70K individuals, there's a 50/50 chance of a collision between any two individuals. – Bohemian Jan 03 '19 at 06:00
  • Does not work with mysql, but with mariadb: https://gist.github.com/BacLuc/0ffb97c8d32b1190b649a955134d0625 – BacLuc Apr 06 '21 at 17:49
  • "So go for hashing method and use that output as a UNIQUE constraint." seems to be from another planet as no one talks about hasing before. Downvote for that. – Andrei Jun 18 '21 at 13:19
27

The limit of 255 for varchar length no longer applies. From the documentation:

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

Unique indexes must have a known maximum length (a requirement of mysql due to its internal implementation), so use varchar with a large enough value to fit your longest expected value, eg

...
`name` varchar(65535) NOT NULL, -- for example 
...
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 2
    Ok but you cannot create a unique key on a varchar(65535) can you? – darma Apr 04 '19 at 10:42
  • 1
    @darma no, not *indexed* columns. The [maximum size for a column with a unique index is varchar(767)](https://stackoverflow.com/q/15157227/256196) – Bohemian Apr 04 '19 at 22:11
-2

I know this is very old but in case anyone else wants to, you can make a varchar or text column unique, you just have to do it after the table creation.

 DROP TABLE IF EXISTS meeting;
CREATE TABLE meeting (
    meeting_id int(11) UNSIGNED UNIQUE NOT NULL PRIMARY KEY AUTO_INCREMENT,
    meeting_name varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
);
CREATE UNIQUE INDEX meeting ON meeting(meeting_name);
harrypottar
  • 7
  • 1