MySQL：#1075 - 不正确的表定义；自动增量与另一个键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8114535/
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: #1075 - Incorrect table definition; autoincrement vs another key?
提问by Haradzieniec
Here is a table in MySQL 5.3.X+ db:
这是 MySQL 5.3.X+ db 中的一个表：
CREATE TABLE members` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`memberid` VARCHAR( 30 ) NOT NULL ,
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`firstname` VARCHAR( 50 ) NULL ,
`lastname` VARCHAR( 50 ) NULL ,
UNIQUE (memberid),
PRIMARY KEY (id)
) ENGINE = MYISAM;
Idcolumn is never used in queries, it is just for visual convenience (so it's easy to see how the table grows). Memberidis an actual key, is unique, and memberidis used in queries to identify any member (WHERE memberid='abcde').
Id列从不用于查询，它只是为了视觉方便（所以很容易看到表是如何增长的）。Memberid是一个实际的键，是唯一的，并且在查询中使用memberid来标识任何成员（WHERE memberid='abcde'）。
My question is: how to keep auto_increment, but make memberid as a primary key? Is that possible? When I try to create this table with PRIMARY KEY (memberid), I get an error:
我的问题是：如何保持auto_increment，但将memberid 作为主键？那可能吗？当我尝试使用PRIMARY KEY (memberid)创建此表时，出现错误：
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
1075 - 不正确的表定义；只能有一个自动列，并且必须将其定义为键
What is the best choice (Hopefully, there is a way to keep id column so performance is good and queries identify any user by memberid, not by id), if the performance is very important (although the disk space is not)?
如果性能非常重要（尽管磁盘空间不是），最好的选择是什么（希望有一种方法可以保留 id 列，以便性能良好并且查询通过 memberid 而不是 id 识别任何用户）？
回答by ypercube??
You canhave an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:
您可以拥有一个不是 的自动递增列，PRIMARY KEY只要其上有索引（键）：
CREATE TABLE members (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
memberid VARCHAR( 30 ) NOT NULL ,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
firstname VARCHAR( 50 ) NULL ,
lastname VARCHAR( 50 ) NULL ,
PRIMARY KEY (memberid) ,
KEY (id) --- or: UNIQUE KEY (id)
) ENGINE = MYISAM;
回答by Thilina Sampath
First create table without auto_increment,
首先创建没有auto_increment的表，
CREATE TABLE `members`(
`id` int(11) NOT NULL,
`memberid` VARCHAR( 30 ) NOT NULL ,
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`firstname` VARCHAR( 50 ) NULL ,
`lastname` VARCHAR( 50 ) NULL
PRIMARY KEY (memberid)
) ENGINE = MYISAM;
after set id as index,
将 id 设置为索引后，
ALTER TABLE `members` ADD INDEX(`id`);
after set id as auto_increment,
将 id 设置为 auto_increment 后，
ALTER TABLE `members` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;
Or
或者
CREATE TABLE IF NOT EXISTS `members` (
`id` int(11) NOT NULL,
`memberid` VARCHAR( 30 ) NOT NULL ,
`Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`firstname` VARCHAR( 50 ) NULL ,
`lastname` VARCHAR( 50 ) NULL,
PRIMARY KEY (`memberid`),
KEY `id` (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
回答by Mike Sherrill 'Cat Recall'
You can make the id the primary key, and set member_id to NOT NULL UNIQUE. (Which you've done.) Columns that are NOT NULL UNIQUEcan be the target of foreign key references, just like a primary key can. (I'm pretty sure that's true of all SQL platforms.)
您可以将 id 设为主键，并将 member_id 设置为NOT NULL UNIQUE。（您已经完成了。）NOT NULL UNIQUE可以作为外键引用的目标的列，就像主键一样。（我很确定所有 SQL 平台都是如此。）
At the conceptual level, there's no difference between PRIMARY KEYand NOT NULL UNIQUE. At the physical level, this is a MySQL issue; other SQL platforms will let you use a sequence without making it the primary key.
在概念层面上，PRIMARY KEY和之间没有区别NOT NULL UNIQUE。在物理层面，这是一个 MySQL 问题；其他 SQL 平台将允许您使用序列而不将其设为主键。
But if performance is really important, you should think twice about widening your table by four bytes per row for that tiny visual convenience. In addition, if you switch to INNODB in order to enforce foreign key constraints, MySQL will use your primary key in a clustered index. Since you're not using your primary key, I imagine that could hurt performance.
但是，如果性能真的很重要，那么您应该三思而后行，为了这种微小的视觉方便，将表格每行扩大 4 个字节。此外，如果您切换到 INNODB 以强制执行外键约束，MySQL 将在聚集索引中使用您的主键。由于您没有使用主键，我想这可能会影响性能。
回答by Vikram Gharge
For the above issue, first of all if suppose tables contains more than 1 primary key then first remove all those primary keys and add first AUTO INCREMENT field as primary key then add another required primary keys which is removed earlier. Set AUTO INCREMENT option for required field from the option area.
对于上述问题，首先，如果假设表包含 1 个以上的主键，则首先删除所有这些主键并添加第一个 AUTO INCREMENT 字段作为主键，然后添加另一个需要的主键，该主键之前已删除。从选项区域为必填字段设置 AUTO INCREMENT 选项。
回答by Md Riasath Arif Prodhan
I think i understand what the reason of your error. First you click auto AUTO INCREMENT field then select it as a primary key.
我想我明白你的错误的原因。首先单击 auto AUTO INCREMENT 字段，然后选择它作为主键。
The Right way is First You have to select it as a primary key then you have to click auto AUTO INCREMENT field.
正确的方法是首先您必须选择它作为主键，然后您必须单击自动 AUTO INCREMENT 字段。
Very easy. Thanks
好简单。谢谢
