make text column as unique key

Question

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

possible duplicate : stackoverflow.com/questions/1827063/… — Tarek
– Tarek, Commented Dec 25, 2012 at 19:22
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
– d-doctor, Commented Dec 25, 2012 at 19:29
You might add a length, like UNIQUE KEY name` (name(100)),` if the first 100 were unique. — Joop Eggen
– Joop Eggen, Commented Dec 25, 2012 at 19:43

Bapi · Accepted Answer · 2021-08-11 12:25:23Z

39

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.

edited Aug 11, 2021 at 12:25

Bapi

3172 silver badges17 bronze badges

answered Dec 25, 2012 at 19:36

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bohemian Over a year ago

Note that technically this is not a solution, because two different text values may generate the same hash - ie a [hash collision](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.

Paul Laffitte Over a year ago

Here is a more deep answer for who want to really understand why mysql behave like this : stackoverflow.com/a/1827099/5677103

hwkd Over a year ago

@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?

Bohemian Over a year ago

@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.

BacLuc Over a year ago

Does not work with mysql, but with mariadb: gist.github.com/BacLuc/0ffb97c8d32b1190b649a955134d0625

|

Bohemian · Accepted Answer · 2012-12-25 19:41:08Z

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

Ok but you cannot create a unique key on a varchar(65535) can you?
@darma no, not indexed columns. The maximum size for a column with a unique index is varchar(767)

harrypottar · Accepted Answer · 2021-09-21 19:27:28Z

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

Collectives™ on Stack Overflow

make text column as unique key

3 Answers 3

6 Comments

2 Comments

1 Comment

Hot Network Questions