创建含主键外键的MySQL表时遇#1005错误，求解决方法

尝试创建以下三张带主键和外键的MySQL表：

CREATE TABLE Book (
    book_id INT,
    book_price DECIMAL(10, 2),
    genre VARCHAR(25),
    book_name VARCHAR(20),
    invoice_id INT,
    PRIMARY KEY (book_id),
    FOREIGN KEY (invoice_id) REFERENCES Invoice(invoice_id)
);

CREATE TABLE Inventory (
    inventory_id INT,
    category VARCHAR(20),
    selling_price DECIMAL(10, 2),
    invoice_id INT,
    PRIMARY KEY (inventory_id),
    FOREIGN KEY (invoice_id) REFERENCES Invoice(invoice_id)
);

CREATE TABLE Invoice (
    invoice_id INT,
    supp_id INT,
    book_id INT,
    inventory_id INT,
    PRIMARY KEY (invoice_id),
    FOREIGN KEY (supp_id) REFERENCES Supplier(supp_id),
    FOREIGN KEY (book_id) REFERENCES Book(book_id),
    FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
);

执行Book表创建语句时，持续报错：

CREATE TABLE Book (
    book_id INT,
    book_price DECIMAL(10, 2),
    genre VARCHAR(25),
    book_name VARCHAR(20),
    invoice_id INT,
    PRIMARY KEY (book_id),
    FOREIGN KEY (invoice_id) REFERENCES Invoice(invoice_id)
);

MySQL提示：

#1005 - Can't create table bookmanagement.book (errno: 150 "Foreign key constraint is incorrectly formed")

试过先创建仅含主键和字段的表，再用ALTER TABLE添加外键，但操作繁琐。想知道如何修正错误，直接创建带外键的表？

出现这个错误的核心原因是循环外键依赖：

• Book表依赖Invoice表的invoice_id
• Invoice表又依赖Book表的book_id和Inventory表的inventory_id
• Inventory表同样依赖Invoice表的invoice_id

MySQL无法在这种循环依赖下直接创建表，因为创建某张表时，被依赖的表还不存在。以下是两种可行的修正方案：

方案1：重构表结构，消除循环依赖（推荐） #

调整关联逻辑，让Invoice作为中间关联表，仅由它依赖其他表，避免双向依赖：

1. 先创建无依赖的基础表Supplier

CREATE TABLE Supplier (
    supp_id INT PRIMARY KEY
);

2. 创建Book和Inventory表（无外键依赖其他待创建表）

CREATE TABLE Book (
    book_id INT PRIMARY KEY,
    book_price DECIMAL(10, 2),
    genre VARCHAR(25),
    book_name VARCHAR(20)
);

CREATE TABLE Inventory (
    inventory_id INT PRIMARY KEY,
    category VARCHAR(20),
    selling_price DECIMAL(10, 2)
);

3. 最后创建Invoice表，关联所有依赖表

CREATE TABLE Invoice (
    invoice_id INT PRIMARY KEY,
    supp_id INT,
    book_id INT,
    inventory_id INT,
    FOREIGN KEY (supp_id) REFERENCES Supplier(supp_id),
    FOREIGN KEY (book_id) REFERENCES Book(book_id),
    FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
);

方案2：保留原有业务关联，简化创建流程 #

如果必须保留Book/Inventory对Invoice的关联，可以先创建Invoice表（暂时不添加对Book和Inventory的外键），再创建另外两张表，最后补全Invoice的外键：

-- 1. 创建Supplier表
CREATE TABLE Supplier (
    supp_id INT PRIMARY KEY
);

-- 2. 创建Invoice表，暂时只添加supp_id的外键
CREATE TABLE Invoice (
    invoice_id INT PRIMARY KEY,
    supp_id INT,
    book_id INT,
    inventory_id INT,
    FOREIGN KEY (supp_id) REFERENCES Supplier(supp_id)
);

-- 3. 创建Book和Inventory表，此时Invoice已存在，可直接添加外键
CREATE TABLE Book (
    book_id INT PRIMARY KEY,
    book_price DECIMAL(10, 2),
    genre VARCHAR(25),
    book_name VARCHAR(20),
    invoice_id INT,
    FOREIGN KEY (invoice_id) REFERENCES Invoice(invoice_id)
);

CREATE TABLE Inventory (
    inventory_id INT PRIMARY KEY,
    category VARCHAR(20),
    selling_price DECIMAL(10, 2),
    invoice_id INT,
    FOREIGN KEY (invoice_id) REFERENCES Invoice(invoice_id)
);

-- 4. 最后给Invoice补全外键
ALTER TABLE Invoice
ADD FOREIGN KEY (book_id) REFERENCES Book(book_id),
ADD FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id);

内容的提问来源于stack exchange，提问作者Nurul Zulaiqha