Alright, let's walk through why you're running into this error and how to fix it properly.

Why the Error Happens #

You're getting this error for two key reasons:

1. Foreign keys require referenced fields to be unique
   In your authors table, you're trying to set book_name as a foreign key pointing to books.book_name. But the book_name column in the books table is just a regular VARCHAR with no UNIQUE constraint or primary key status. Databases enforce that foreign keys reference fields that can uniquely identify a single row in the referenced table—otherwise, it can't tell which book you're linking to.

2. Your table design has logical flaws
   Storing and linking by names (like book_name or author_name) is a bad practice. Names aren't unique—you could easily have two books with the same title, or two authors with the same name. This will lead to duplicate data, conflicts, and broken relationships down the line. Plus, your current setup has the relationship backwards: an author can write multiple books, so the book should reference the author, not the other way around.

The Correct Fix (Proper Database Design) #

Let's restructure your tables to use unique IDs for relationships, which is the standard and most reliable approach:

Step 1: Create the authors table first (since books will reference it) #

CREATE TABLE authors (
    author_id INT NOT NULL AUTO_INCREMENT,
    author_name VARCHAR(255) NOT NULL,
    author_surname VARCHAR(255) NOT NULL,
    PRIMARY KEY (author_id)
);

Step 2: Create the books table with a foreign key to authors #

CREATE TABLE books (
    book_id INT NOT NULL AUTO_INCREMENT,
    book_name VARCHAR(255) NOT NULL,
    book_description VARCHAR(255) NOT NULL,
    book_number_of_pages INT NOT NULL,
    book_state VARCHAR(255) NOT NULL,
    author_id INT NOT NULL, -- Store the author's unique ID instead of their name
    PRIMARY KEY (book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

Why This Works #

• authors.author_id is a primary key, so it's guaranteed to be unique—perfect for a foreign key reference.
• The relationship makes logical sense: a book belongs to one author, and an author can have many books.
• No duplicate data or name-based conflicts, since we're using unique IDs for linking.

If You Insist on Your Original Structure (Not Recommended) #

If you need to keep the foreign key pointing from authors to books.book_name, you must add a UNIQUE constraint to books.book_name to make it a valid referenced field:

-- Drop existing tables first if they exist
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS books;

CREATE TABLE books (
    book_id INT NOT NULL AUTO_INCREMENT,
    book_name VARCHAR(255) NOT NULL,
    book_description VARCHAR(255) NOT NULL,
    book_number_of_pages INT NOT NULL,
    book_state VARCHAR(255) NOT NULL,
    author_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (book_id),
    UNIQUE KEY (book_name) -- Add this unique constraint to make book_name referenceable
);

CREATE TABLE authors (
    author_id INT NOT NULL AUTO_INCREMENT,
    author_name VARCHAR(255) NOT NULL,
    author_surname VARCHAR(255) NOT NULL,
    book_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (author_id),
    FOREIGN KEY (book_name) REFERENCES books(book_name) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

⚠️ Note: This is not ideal because you can't have two books with the same title, and you're storing redundant data (author name in both tables) which can lead to inconsistencies. Stick with the ID-based approach whenever possible.

内容的提问来源于stack exchange，提问作者Karen Mnatsakanian