MySQL:错误1022(23000):无法写入；表'#sql-2b8_2'中的重复键 [英] MySQL: ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2b8_2'

问题描述

我将实现一个书店数据库.我已经创建了表book，author和publisher.我想建立以下两个关系.

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 is written by Author.
Book is published by Publisher.

为了实现这些关系，我编写了一些SQL语句，例如:

In order to implement these relationships, I write some SQL statements like:

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);

当MySQL Shell执行最后一个alter语句时，出现此错误.

When MySQL shell executes the last alter statement, I get this error.

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.

推荐答案

您正在获取duplicate key error，因为在您对author表的第一个alter语句中，数据库中已经存在一个名为ISBN的约束

You are getting the duplicate key error cause there is already a constraint named ISBN present in database per your first alter statement to author table

alter table author add constraint ISBN foreign key (ISBN) references book (ISBN);

尝试对Publisher表中的约束使用其他名称

Try using a different name for the constraint in Publisher table

alter table publisher add constraint ISBN1 
foreign key (ISBN) references book (ISBN);

这篇关于MySQL:错误1022(23000):无法写入；表'#sql-2b8_2'中的重复键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！