database - Mysql - Add auto_increment to primary key -


i have strange problem mysql.

i trying alter table's column primary key , has auto_increment constraint defined on it. foreign key reference multiple other tables. need change length of column in both , parent , children.

set foreign_key_checks=0; alter table parent  modify identifier smallint(10) unsigned; alter table child_1 modify fk_identifier smallint(10) unsigned; alter table child_2 modify fk_identifier smallint(10) unsigned; alter table child_3 modify fk_identifier  smallint(10) unsigned; alter table child_4 modify fk_identifier smallint(10) unsigned; alter table child_5 modify fk_identifier smallint(10) unsigned; set foreign_key_checks=1; 

this removes auto increment on parent table. best way add constraint ?

the below seems failing.

mysql> alter table parent modify identifier smallint(10) primary key auto_increment; error 1068 (42000): multiple primary key defined   alter table parent modify identifier smallint(10) auto_increment; ------------------------ latest foreign key error ------------------------ 110125 15:49:08 error in foreign key constraint of table db/child_1: there no index in referenced table contain columns first columns, or data types in referenced table not match ones in table. constraint: ,   constraint child_1_ibfk_1 foreign key (fk_identifier) references roomprofile (identifier) on delete cascade on update cascade index in foreign key in table primary 

is there better way achieve ?

edit : show create (after alter) :

  create table `parent` (   `identifier` smallint(10) unsigned not null default '0',   `name` varchar(20) default null,   `description` varchar(100) default null,    primary key  (`identifier`),    unique key `name` (`name`),   ) engine=innodb default charset=latin1 |  

before alter

`identifier` smallint(5) unsigned not null auto_increment, 

thanks!

you don't need specify primary key in modify statement:

alter table parent modify identifier smallint(10) auto_increment; 

Comments

Popular posts from this blog

java - SNMP4J General Variable Binding Error -

windows - Python Service Installation - "Could not find PythonClass entry" -

Determine if a XmlNode is empty or null in C#? -