[ACCEPTED]-MySQL: ERROR 1022 (23000): Can't write; duplicate key in table '#sql-2b8_2'-sql
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
alter table author add constraint ISBN foreign key (ISBN) references book (ISBN);
Try using 1 a different name for the constraint in
alter table publisher add constraint ISBN1 foreign key (ISBN) references book (ISBN);
Your data structure is strange. You should 14 have entity tables for
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
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]
try this Alter statement,
alter table publisher add constraint ISBN_publisher foreign key (ISBN) references book (ISBN);
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):
The most likely you already have a constraint 4 with the name ISBN in your database. Just 3 rename the constraints if so.
alter 2 table publisher add constraint P_ISBN foreign 1 key (ISBN) references book (ISBN);
I think the message could do with being 2 more relevant. It has nothing to do with 1 namespace.
More Related questions