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

Accepted answer
Score: 37

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

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

Try using 1 a different name for the constraint in Publisher table

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

Your data structure is strange. You should 14 have entity tables for Books, Authors, and Publishers. These 13 would have auto-incremented ids as primary 12 keys and additional information. For instance, books 11 have "titles" and "isbn" numbers. Authors 10 have names. Publishers have names and addresses.

Then 9 you want junction tables. So, books have 8 one or more authors (ignoring "editors" that 7 compile chapters from other authors), and 6 authors can write one or more books. This 5 suggests a BookAuthors table, with one row per book 4 and per author in the book.

Books would generally 3 have one publisher, so this is a one-to-many 2 relationship. You can implement this by 1 having PublisherId in the Books table.

Score: 2

Foreign key Constraint names in MySQL has global visibility 3 and hence should be unique. So better adopt 2 a naming pattern like fk_[table name]_[field 1 name]

Score: 1

try this Alter statement,

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


Score: 1

As addition to other answers, if other answers 4 don't make it work - check that your constraint 3 are short enough.

Example: I was getting an error 2 with these:


but not with these (I shortened 1 the names):

Score: 0

The most likely you already have a constraint 4 with the name ISBN in your database. Just 3 rename the constraints if so.

Try This

alter 2 table publisher add constraint P_ISBN foreign 1 key (ISBN) references book (ISBN);

Score: 0

I think the message could do with being 2 more relevant. It has nothing to do with 1 namespace.

More Related questions