Q:

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

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.

A:

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

A:

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.
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 BookAuthors table, with one row per book and per author in the book.
Books would generally have one publisher, so this is a one-to-many relationship.  You can implement this by having PublisherId in the Books table.

