Mysql - 将 auto_increment 添加到主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4795382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql - Add auto_increment to primary key
提问by Ricko M
I have a strange problem with mysql.
我对 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.
我试图改变一个表的列，它是一个主键，并在其上定义了一个 auto_increment 约束。这也是多个其他表的外键引用。我需要在父项和所有子项中更改此列的长度。
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!
谢谢！
回答by Arnaud Le Blanc
You don't need to specify PRIMARY KEYin the MODIFY statement:
您不需要PRIMARY KEY在 MODIFY 语句中指定：
ALTER TABLE Parent MODIFY Identifier smallint(10) AUTO_INCREMENT;
