mysql - 由于外键无法创建表

Question

我这里好像不能创建第三张表。这是怎么回事？我收到通用的无法创建表 errno 150 消息。好像跟外键有关系

餐 table 食谱

CREATE TABLE recipe(
    recipe_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    description VARCHAR(40) NOT NULL,
    PRIMARY KEY (recipe_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

表 ingredient_type

CREATE TABLE ingredient_type(
    ingredient_type_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ingredient_type VARCHAR(40) NOT NULL,
    description VARCHAR(40) NOT NULL,
    PRIMARY KEY (ingredient_type_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

餐 table 食材

CREATE TABLE ingredient(
    ingredient_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ingredient_type_id INT(10) NOT NULL,
    name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    FOREIGN KEY (ingredient_type_id) REFERENCES ingredient_type (ingredient_type_id),
    PRIMARY KEY (ingredient_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Answer 1

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Source: FOREIGN KEY Constraints in the MySQL manual

您的代码中的问题是 ingredient_type.ingredient_type_id 是 unsigned，但 ingredient.ingredient_type_id 不是。