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
Post a Comment