Page 1 of 1

Error : #1071 - Specified key was too long

Posted: Thu Nov 30, 2006 12:34 am
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 ?

Posted: Thu Nov 30, 2006 12:49 am
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'

Posted: Thu Nov 30, 2006 1:09 am
by dibyendrah
After executing different queries, I have found that that error occurs only UTF8 character set is being used as database character set.