OS : XP
Web server : IIS 5.1
PHP : 5.2.0 en cgi (php.exe)
MySQL server : 4.1.22-community-nt
MySQL client : 4.1.7
Browser : Firefox 2.0.0.9
phpmyadmin : 2.11.0 (ok sorry I say always tomorrow)
Using gui interface of phpmyadmin I try to create a table with 3 first fields in primary key. The third one is INT unsigned autoincremented.
The generated SQL source is wrong and I got en error.
The generated SQL source is :
CREATE TABLE `bdp_ally`.`attaques` (
`attaquant` VARCHAR( 24 ) NOT NULL ,
`cible` VARCHAR( 24 ) NOT NULL ,
`num` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`date_heure` DATETIME NOT NULL ,
`type` ENUM( 'duel', 'brulot', 'port' ) NOT NULL ,
`resultat` ENUM( 'reussite', 'echec', 'nul' ) NOT NULL ,
`degats_id` INT( 11 ) NULL ,
`attaq_ally` VARCHAR( 24 ) NULL ,
`cible_ally` VARCHAR( 24 ) NULL ,
PRIMARY KEY ( `attaquant` , `cible` )
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_cs
The autoincrement directive take precedence or is independent of the processing of the primary key fields list. This is wrong and the correct code should be :
CREATE TABLE `bdp_ally`.`attaques` (
`attaquant` VARCHAR( 24 ) NOT NULL ,
`cible` VARCHAR( 24 ) NOT NULL ,
`num` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`date_heure` DATETIME NOT NULL ,
`type` ENUM( 'duel', 'brulot', 'port' ) NOT NULL ,
`resultat` ENUM( 'reussite', 'echec', 'nul' ) NOT NULL ,
`degats_id` INT( 11 ) NULL ,
`attaq_ally` VARCHAR( 24 ) NULL ,
`cible_ally` VARCHAR( 24 ) NULL ,
PRIMARY KEY ( `attaquant` , `cible` , `num` )
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_cs
Logged In: YES
user_id=210714
Originator: NO
Fixed in subversion, thanks for reporting.