New to SQL - Trying to add a foreign key to my table but getting error (key column ' ' doe-CodePudding

I'm pretty new to SQL and I am trying to create a recipe database. Currently I have 2 tables set up that I am trying to join with a foreign key but I keep getting an error when trying to add the key.

First table:

CREATE TABLE recipe(
recipeID INT UNSIGNED NOT NULL AUTO_INCREMENT,
recipeName VARCHAR(255),
created TIMESTAMP,
updated TIMESTAMP,
PRIMARY KEY (recipeID)
);

Second table:

CREATE TABLE instructions (
instructionsID INT UNSIGNED NOT NULL AUTO_INCREMENT,
specification LONGTEXT,
listOrder INT,
PRIMARY KEY (instructionsID),
FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)
);

Error: ERROR 1072 (42000): Key column 'recipeID' doesn't exist in table

recipe Table info:

Field	Type	Null	Key	Default	Extra
recipeID	int(10) unsigned	NO	PRI	NULL	auto_increment
recipeName	varchar(255)	YES		NULL
created	timestamp	NO		current_timestamp()	on update current_timestamp()
updated	timestamp	NO		0000-00-00 00:00:00

instructions Table info:

Field	Type	Null	Key	Default	Extra
instructionsID	int(10) unsigned	NO	PRI	NULL	auto_increment
specification	longtext	YES		NULL
listOrder	int(11)	YES		NULL

CodePudding user response：

FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)

Here you are referencing recipeID from recipe to recipeID in instructions table. You don't have a recipeID in instructions table.I think you need to create recipeID column first and then reference it with recipeID in recipe table

 CREATE TABLE instructions (
    instructionsID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    specification LONGTEXT,
    listOrder INT,
    recipeID INT UNSIGNED,
    PRIMARY KEY (instructionsID),
    FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)
    );

THis will work i guess.