MySQL错误#1215:无法添加外键约束

Question

我正在尝试实现图中所示的表：这张照片(数据库方案取自http://www.databaseanswers.org/data_models/recipes/index.htm)。

但是我的Recipe_step_ingredients表有问题。当添加外键约束时，recipe_steps_id约束会出现错误#1215(当不添加该约束时，我不会得到错误，我可以添加其他两个约束(。

以下是创建表格的may命令：

CREATE TABLE recipe_step_ingredients (
recipe_id INTEGER,
step_number INTEGER,
ingredient_id INTEGER,
amount_required VARCHAR(128),

CONSTRAINT FOREIGN KEY (recipe_id) REFERENCES recipe_steps (recipe_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (step_number) REFERENCES recipe_steps (step_number) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (ingredient_id) REFERENCES ingredients (ingredient_id) ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (recipe_id, step_number, ingredient_id)
) ENGINE = INNODB

有人知道它可能有什么问题吗？

编辑：更改代码以匹配图片中的命名约定。此外，这是我使用的DDL的其余部分：

CREATE TABLE recipes (
recipe_id INTEGER NOT NULL AUTO_INCREMENT,
recipe_name VARCHAR(128),
recipe_description VARCHAR(4096),

PRIMARY KEY (recipe_id),
INDEX (recipe_name)
) ENGINE = INNODB
CREATE TABLE recipe_steps (
recipe_id INTEGER,
step_number INTEGER,
instructions VARCHAR(4096),

CONSTRAINT FOREIGN KEY (recipe_id) REFERENCES recipes (recipe_id) ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (recipe_id, step_number)
) ENGINE = INNODB
CREATE TABLE ingredient_types (
ingredient_type_id INTEGER NOT NULL AUTO_INCREMENT,
ingredient_type_description VARCHAR(4096),

PRIMARY KEY (ingredient_type_id)
) ENGINE = INNODB
ALTER TABLE ingredient_types ADD COLUMN ingredient_type_name VARCHAR(128);
ALTER TABLE ingredient_types ADD INDEX(ingredient_type_name);
CREATE TABLE ingredients (
ingredient_id INTEGER NOT NULL AUTO_INCREMENT,
ingredient_type_id INTEGER,
ingredient_name VARCHAR(128),

CONSTRAINT FOREIGN KEY (ingredient_type_id) REFERENCES ingredient_types (ingredient_type_id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (ingredient_id),
INDEX (ingredient_name)
) ENGINE = INNODB
CREATE TABLE recipe_step_ingredients (
recipe_id INTEGER,
step_number INTEGER,
ingredient_id INTEGER,
amount_required VARCHAR(128),

CONSTRAINT FOREIGN KEY (recipe_id) REFERENCES recipe_steps (recipe_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (step_number) REFERENCES recipe_steps (step_number) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (ingredient_id) REFERENCES ingredients (ingredient_id) ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY (recipe_id, step_number, ingredient_id)
) ENGINE = INNODB

Answer 1

确保：

• 外键引用被引用关系的键的所有属性(不能只引用其中的一些(。所以，如果R的键是(A，B(，你应该同时引用A和B。你不能只引用A或只引用B
• 在外键约束中引用的属性被定义为UNIQUE或是关系的主键。在Mysql中，这也可以通过定义KEY或UNIQUE KEY属性来实现
• 如果引用主键，则不需要在表后面指示属性的名称(默认为主键(

在您的情况下，

• 确保表recipe_steps具有属性recipe_steps_id，并且
• 该属性是UNIQUE KEY、KEY或关系的主键
• 此外，请确保已创建(键的(相应索引