MySQL无法使列auto_increment [英] MySql can't make column auto_increment
问题描述
我有一个表"Bestelling",其中有4列:"Id"(PK),"KlantId","Datum","BestellingsTypeId",现在我想使列ID为auto_increment,但是当我尝试这样做时那,我得到这个错误:
I have a table "Bestelling" with 4 columns: "Id" (PK), "KlantId", "Datum", "BestellingsTypeId", now I want to make the column Id auto_increment, however, when I try to do that, I get this error:
ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `aafest`.`aafest_bestelling` CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT
ERROR: Error when running failback script. Details follow.
ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `aafest_bestelling` (
`Id` int(11) NOT NULL,
`KlantId` int(11) DEFAULT NULL,
`Datum` date DEFAULT NULL,
`BestellingstypeId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
有人知道吗?
推荐答案
如果表包含ID为0(或负数)的现有记录,则会发生这种情况.更新所有现有记录以使用正值将允许在该列上设置auto_increment.
This will happen if the table contains an existing record with an id of 0 (or negative). Updating all existing records to use positive values will allow auto_increment to be set on that column.
有人问0是如何到达那里的.为了澄清起见,《 MySQL参考手册》指出:对于数字类型,默认值为0,但对于用AUTO_INCREMENT属性声明的整数或浮点类型,默认值为序列中的下一个值."因此,如果在启用auto_increment之前对表执行插入操作时未提供数值列的值,则在插入过程中将使用默认值0.可以在 https://dev.mysql.com/doc/中找到更多详细信息. refman/5.0/en/data-type-defaults.html .
Some people asked how that 0 got in there. For clarification, the MySQL Reference Manual states that "For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence." So, if you performed an insert on a table without providing a value for the numeric column before the auto_increment was enabled, then the default 0 would be used during the insert. More details may be found at https://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html.
这篇关于MySQL无法使列auto_increment的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!