Error : #1071 - Specified key was too long

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Error : #1071 - Specified key was too long

Post by dibyendrah »

Dear all,
I have created a table like below with MyISAM engine and shows error :

Code: Select all

#1071 - Specified key was too long; max key length is 1000 bytes
MyISAM

Code: Select all

CREATE TABLE `tbl_pos_main_cat` (
`pos_main_cat_id` TINYINT( 4 ) NOT NULL AUTO_INCREMENT ,
`pos_main_cat_sf` VARCHAR( 255 ) NOT NULL DEFAULT '',
`pos_main_cat_ff` VARCHAR( 255 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `pos_main_cat_id` ) ,
UNIQUE KEY `UNIQUE_tbl_pos_main_cat` ( `pos_main_cat_sf` , `pos_main_cat_ff` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 ROW_FORMAT = COMPACT COMMENT = 'Part-of-Speech Main Category'

But while using InnoDB table engine, it works fine.

InnoDB

Code: Select all

CREATE TABLE `tbl_pos_main_cat` (
`pos_main_cat_id` TINYINT( 4 ) NOT NULL AUTO_INCREMENT ,
`pos_main_cat_sf` VARCHAR( 255 ) NOT NULL DEFAULT '',
`pos_main_cat_ff` VARCHAR( 255 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `pos_main_cat_id` ) ,
UNIQUE KEY `UNIQUE_tbl_pos_main_cat` ( `pos_main_cat_sf` , `pos_main_cat_ff` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ROW_FORMAT = COMPACT COMMENT = 'Part-of-Speech Main Category'
Are therey any issues on key length on MyISAM table engine ?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Actually, I have made the combination of two fields as unique key which works fine with InnoDB table engine. But that fails using MyISAM table engine.

This works fine with MyISAM table engine

Code: Select all

CREATE TABLE `tbl_pos_main_cat` (
`pos_main_cat_id` TINYINT( 4 ) NOT NULL AUTO_INCREMENT ,
`pos_main_cat_sf` VARCHAR( 255 ) NOT NULL DEFAULT '',
`pos_main_cat_ff` VARCHAR( 255 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `pos_main_cat_id` ) ,
UNIQUE KEY `UNIQUE_tbl_pos_main_cat` ( `pos_main_cat_sf` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 ROW_FORMAT = COMPACT COMMENT = 'Part-of-Speech Main Category'
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

After executing different queries, I have found that that error occurs only UTF8 character set is being used as database character set.
Post Reply