Home New to SQL – Trying to add a foreign key to my table but getting error (key column ' ' does not exist)

Questions

New to SQL – Trying to add a foreign key to my table but getting error (key column ' ' does not exist)

February 5, 2022

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

>Solution :

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.