MySQL: ERROR 1022 (23000): 无法写入；表'#sql-2b8_2'中的重复键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23954131/
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: ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2b8_2'
提问by inherithandle
I'm going to implement a bookstore database. I have created the table book, author, and publisher. I'd like to make the following two relationships.
我要实现一个书店数据库。我创建了表book，author和publisher。我想建立以下两种关系。
Book is written by Author.
Book is published by Publisher.
In order to implement these relationships, I write some SQL statements like:
为了实现这些关系，我编写了一些 SQL 语句，例如：
create table book(
ISBN varchar(30) NOT NULL,
title varchar(30) not null,
author varchar(30) not null,
stock Int,
price Int,
category varchar(30),
PRIMARY KEY ( ISBN )
);
create table author(
author_id int not null auto_increment,
author_name varchar(15) NOT NULL,
address varchar(50) not null,
ISBN varchar(30) not null,
primary key (author_id)
);
alter table author add constraint ISBN foreign key (ISBN) references book (ISBN);
create table publisher(
publisher_id int not null auto_increment,
publisher_name varchar(15) NOT NULL,
address varchar(50) not null,
ISBN varchar(30) not null,
primary key (publisher_id)
);
alter table publisher add constraint ISBN foreign key (ISBN) references book (ISBN);
When MySQL shell executes the last alterstatement, I get this error.
当 MySQL shell 执行最后一条alter语句时，我收到此错误。
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2b8_2'
Originally, can't foreign key be designated two times? What's wrong with? Thank you in advance.
原来，外键不能指定两次吗？怎么了？先感谢您。
回答by Rahul
You are getting the duplicate key errorcause there is already a constraint named ISBNpresent in database per your first alterstatement to authortable
您得到的duplicate key error原因是ISBN，根据您alter对author表的第一条语句，数据库中已经存在一个名为的约束
alter table author add constraint ISBN foreign key (ISBN) references book (ISBN);
Try using a different name for the constraint in Publishertable
尝试为Publisher表中的约束使用不同的名称
alter table publisher add constraint ISBN1
foreign key (ISBN) references book (ISBN);
回答by Gordon Linoff
Your data structure is strange. You should have entity tables for Books, Authors, and Publishers. These would have auto-incremented ids as primary keys and additional information. For instance, books have "titles" and "isbn" numbers. Authors have names. Publishers have names and addresses.
你的数据结构很奇怪。你应该有实体的表Books，Authors和Publishers。这些将自动增加 id 作为主键和附加信息。例如，书籍有“书名”和“isbn”编号。作者有名字。出版商有名称和地址。
Then you want junction tables. So, books have one or more authors (ignoring "editors" that compile chapters from other authors), and authors can write one or more books. This suggests a BookAuthorstable, with one row per book and per author in the book.
然后你想要连接表。因此，书籍有一个或多个作者（忽略编译其他作者章节的“编辑器”），并且作者可以写一本书或多本书。这建议了一个BookAuthors表格，每本书和书中的每个作者都有一行。
Books would generally have one publisher, so this is a one-to-many relationship. You can implement this by having PublisherIdin the Bookstable.
书籍通常会有一个出版商，所以这是一对多的关系。您可以通过表PublisherId中的内容来实现这一点Books。
回答by randomness
Foreign key Constraint namesin MySQL has global visibility and hence should be unique. So better adopt a naming pattern like fk_[table name]_[field name]
MySQL 中的外键约束名称具有全局可见性，因此应该是唯一的。所以最好采用像 fk_[table name]_[field name] 这样的命名模式
回答by Dennis
As addition to other answers, if other answers don't make it work - check that your constraint are short enough.
作为其他答案的补充，如果其他答案不起作用 - 检查您的约束是否足够短。
Example:I was getting an error with these:
示例：我遇到了以下错误：
fk_test_group_has_test_group1`
fk_test_group_has_test_header1
but not with these (I shortened the names):
但不是这些（我缩短了名称）：
fk_test_group_has_group1`
fk_test_group_has_header1
回答by ravikumar
try this Alter statement,
试试这个Alter语句，
alter table publisher add constraint
ISBN_publisher foreign key (ISBN) references book (ISBN);
回答by Jak Pren
The most likely you already have a constraint with the name ISBN in your database. Just rename the constraints if so.
您的数据库中很可能已经有一个名称为 ISBN 的约束。如果是这样，只需重命名约束。
Try This
尝试这个
alter table publisher add constraint P_ISBN foreign key (ISBN) references book (ISBN);
更改表发布者添加约束 P_ISBN 外键 (ISBN) 参考书 (ISBN)；
回答by Dave Hodgkinson
I think the message could do with being more relevant. It has nothing to do with namespace.
我认为该消息可能与更相关。它与命名空间无关。
