Mysql - Add auto_increment to primary key
51,096
You don't need to specify PRIMARY KEY
in the MODIFY statement:
ALTER TABLE Parent MODIFY Identifier smallint(10) AUTO_INCREMENT;
Comments
-
Ricko M almost 2 years
I have a strange problem with mysql.
I am trying to alter a table's column which is a primary key and has an auto_increment constraint defined on it. This is also a foreign key reference for multiple other tables. I need to change the length of this column in both , parent and all 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 the auto increment on the parent table. What would be the best way to add the constraint back ?
The below seems to be 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 is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match to the ones in table. Constraint: , CONSTRAINT Child_1_ibfk_1 FOREIGN KEY (FK_Identifier) REFERENCES RoomProfile (Identifier) ON DELETE CASCADE ON UPDATE CASCADE The index in the foreign key in table is PRIMARY
Is there a better way to achieve this ?
Edit : Show create is (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!
-
Mchl over 13 yearsTry disabling
foreign_key_checks
when running this. -
Ricko M over 13 yearssetting set foreign_key_checks=0; and running the above has no effect. :(
-
Ricko M over 13 yearsmissing unsigned from datatype :)
-
Pacerier about 9 years@arnaud576875, Weird,Why don't we need to specify
PRIMARY KEY
in themodify
statement? why are the keys still lying around after they columns were modified? -
Peter about 7 yearsThis is not working at all! Anyone that has a solution?