[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:

fk_test_group_has_test_group1`
fk_test_group_has_test_header1

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

fk_test_group_has_group1`
fk_test_group_has_header1

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.

Source: stackoverflow.com

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

Foreign keys in MySQL?

MySQL remove duplicates from big database quick

Should I add this new column to customers table or to a separate new table?

Converting date to this format

query " ALTER TABLE test_posts ADD sticky boolean NOT NULL default = false" = error

MySQL: Name primary key in CREATE TABLE statement

MySQL IN with LIKE

Change Data Capture in MySQL

Selecting all items in one table and join with another table, allowing nulls

My SQL Dynamic query execute and get ouput into a variable in stored procedure