Mysql - 将auto_increment添加到主键 [英] Mysql - Add auto_increment to primary key

问题描述

我有一个奇怪的问题与mysql。

我试图改变一个表的列是一个主键，并定义了一个auto_increment约束。这也是多个其他表的外键参考。
我需要改变父列和所有子列中此列的长度。

  set foreign_key_checks = 0; 
 alter table父修改标识符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修改FK_Identifier smallint（10）unsigned; 
 alter table Child_5 modify FK_Identifier smallint（10）unsigned; 
 set foreign_key_checks = 1; 
  

这将删除父表上的自动增量。

以下似乎是失败的。

  mysql> ALTER TABLE parent MODIFY标识符smallint（10）PRIMARY KEY AUTO_INCREMENT; 
错误1068（42000）：定义多个主键
 
 
 ALTER TABLE父MODFY标识符smallint（10）AUTO_INCREMENT; 
 ------------------------ 
最新外部键错误
 ---------- -------------- 
 110125 15:49:08表db / Child_1的外键约束错误：
在引用表中没有索引，它将包含
列作为第一列，或
引用表中的数据类型与表中的数据类型不匹配。约束：
，
 CONSTRAINT Child_1_ibfk_1 FOREIGN KEY（FK_Identifier）REFERENCES RoomProfile（Identifier）ON删除CASCADE ON UPDATE CASCADE 
表中的外键索引为PRIMARY 
  
 
 
编辑：显示创建是（改变后）：
  CREATE TABLE`parent`（
`Identifier` smallint默认值'0'，
`Name` varchar（20）default NULL，
`说明`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，
  
 
 
解决方案
您不需要在MODIFY语句中指定 PRIMARY KEY  
  ALTER TABLE父MODIFY标识符smallint（10）AUTO_INCREMENT; 
  
 
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!
解决方案
You don't need to specify PRIMARY KEY in the MODIFY statement:
ALTER TABLE Parent MODIFY Identifier smallint(10) AUTO_INCREMENT;


                        
这篇关于Mysql  - 将auto_increment添加到主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！