CODEWITHSUNDEEP
mysql
Tigran
Tigran

Reputation: 1057

MySQL unique 1500 varchar field error (#1071 - Specified key was too long)

I have a field (link) that is varchar (1500) and that I want to make unique. I applied changes to mysql configuaration and increased length to 3072 bytes 

ROW_FORMAT=DYNAMIC, innodb_file_format = Barracuda, innodb_large_prefix = true

But when I apply unique to my field, I got next error:

"#1071 - Specified key was too long; max key length is 3072 bytes"

My field is varchar(1500) that is 3000 bytes.

What's wrong?

Update (1) Table data:

CREATE TABLE IF NOT EXISTS `pages` (
  `link` varchar(1500) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `lastvisited` datetime DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `link` (`link`(255))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

Update (2) Alter command (done via PHPMYADMIN)

ALTER TABLE  `pages` ADD UNIQUE (
`link`
)

Upvotes: 2

Views: 1345

Answers (1)

lanzz
lanzz

Reputation: 43168

Since you will be storing URLs in the link column, you don't actually need to use UTF8 for it, because URLs can contain only ASCII characters. Specifying a plain ASCII character encoding for your link column will even allow you to raise its max length to 3072 characters.

CREATE TABLE IF NOT EXISTS `pages` (
  `link` varchar(1500) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `domain` varchar(255) NOT NULL,
  `lastvisited` datetime DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `link` (`link`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC;

(Updated as per @eggyal's suggestion for the ascii_bin collation)

Upvotes: 3

Related Questions