List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:February 16 2006 12:12pm
Subject:Re: primary key
View as plain text  
Ronan <r.mcglue@stripped> wrote on 16/02/2006 11:56:18:

> Im trying to set up a primary key of server(text), date (date), hour 
> (small int) but when i try to include the server field in the key it 
> replies with
> 
> ALTER TABLE `exim` DROP PRIMARY KEY ,
> ADD PRIMARY KEY ( `date` , `hour` , `server` )
> 
> #1170 - BLOB/TEXT column 'server' used in key specification without a 
> key length
> 
> i have googled, but not much is relevant to my example i dont think..
> 
> 
> CREATE TABLE `exim` (
>    `date` date NOT NULL default '0000-00-00',
>    `server` longtext NOT NULL,
>    `hour` tinyint(4) NOT NULL default '0',
>    `count` smallint(6) NOT NULL default '0',
>    PRIMARY KEY  (`date`,`hour`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats';

A LONGTEXT field may be up to 4 Gigabytes long. MySQL cannot (and, I would 
think, should not) include thenwhole 4Gb in the index. It therefore needs 
you to indicate how many characters of the server field it should actually 
use in the index. To get it to use only the first 64 characters in the 
key, you should put in server(64) (the single quotes you are using are 
necesary only if you wish to give a colum the same name as a reserved 
wioord - a vary bad practice). 

However, if you are using it as a PRIMARY KEY or UNIQUE KEY, *you* must 
guarantee that those firat 64 characters are unique. within any given date 
and hour i.e., I would guess, that your servers are unique within the 
first 64 (or however many you choose) characters.

May I suggest that a more conventional way to do what I think you are 
doing woiuld be to have two tables. Allocate each server a number, and put 
the number in the exim table. Then have another table to convert the 
server name to a number. It is then trivially easy to use that table to 
convert from server number to name or vice versa. And the server name no 
l;onger has to be unique in the first N characters: as long as the names 
differ, the table will work.

        Alec
Thread
primary keyRonan16 Feb
  • Re: primary keyAlec.Cawley16 Feb
  • Re: primary keyMartijn Tonies16 Feb