Jon Wynacht <[EMAIL PROTECTED]> wrote on 02/01/2005 03:33:13 PM: > Hi, > > I'm wondering if anybody can help me decrypt what this error code is > referring to: > > ERROR 1071: Specified key was too long. Max key length is 500 > > This is the offending table creation script: > > CREATE TABLE `APPROVAL` ( > `APPROVAL_ID` mediumint(9) unsigned NOT NULL auto_increment, > `APPROVAL_CURRENT_LEVEL` mediumint(8) unsigned NOT NULL default '1', > `APPROVAL_HIGHEST_LEVEL` mediumint(8) unsigned NOT NULL default '1', > `APPROVAL_STATUS` varchar(255) NOT NULL default '', > `APPROVAL_SUB_STATUS` varchar(255) default NULL, > `APPROVAL_APPLICATION` varchar(255) NOT NULL default '', > `FOREIGN_ID` mediumint(9) unsigned NOT NULL default '0', > `FOREIGN_PARENT_ID` mediumint(9) NOT NULL default '0', > `FOREIGN_TYPE` varchar(255) NOT NULL default '', > `CREATED_BY` mediumint(9) NOT NULL default '0', > `CREATED_DT` date NOT NULL default '0000-00-00', > `MODIFIED_BY` mediumint(9) default NULL, > `MODIFIED_TS` timestamp(14) NOT NULL, > PRIMARY KEY (`APPROVAL_ID`), > UNIQUE KEY `APPROVAL_APPLICATION` > (`APPROVAL_APPLICATION`,`FOREIGN_ID`,`FOREIGN_TYPE`) > ) TYPE=InnoDB ROW_FORMAT=DYNAMIC; > > Is it the unique key I'm creating and if so how do I get around that? > > Thanks, > > Jon >
You are correct. Your UNIQUE key definition is failing because it cannot be composed of more than 500 characters. If I add up the display sizes of the columns you want to use to build your UNIQUE index from I get 519 bytes(255+9+255 = 519). May I suggest that you do not use varchar(255) for those fields? Using varchar(200) (or something MUCH smaller, perhaps) should suffice to store the actual data values you need in those fields. Then you could create your UNIQUE index without going over the 500 character limit. The alternative is to only index a portion of your varchar(255) fields. You do that by putting the length of the field after the field name enclosed by parentheses. ( http://dev.mysql.com/doc/mysql/en/create-index.html) You would do something like this: UNIQUE (`APPROVAL_APPLICATION` (200), `FOREIGN_ID`, `FOREIGN_TYPE` (200)) That still creates an index with up to 409 characters in it. This is a rather WIDE index entry and will slow you down when it comes time to search it. I highly recommend reducing the sizes of the fields to what you will need to store the actual data. IF you don't know (because you are importing some unknown data) then save the creation of the UNIQUE index until after you have read in all of the data. That way you will know the true sizes of the data for those fields. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
