MySQL: ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2b8_2'

Question

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.

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

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.

Answer 1

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

Try using a different name for the constraint in Publisher table

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