Error: '1064 (42000): You have an error in your SQL syntax

December 27, 2021

I’m coding in Python using MYSQL to create a database recipe. I have written some variables to create different tables. All could be created except one that caused an error :

create_recipe_ingredient_table = """
CREATE TABLE IF NOT EXISTS recipe_ingredient (
    recipe_ingredient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount INT,
    recipe_id INT,
    ingredient_id INT,
    measure_id INT,
    FOREIGN KEY (recipe_id) REFERENCES recipe(recipe_id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id),
    FOREIGN KEY (measure_id) REFERENCES measure(measure_id)
    """

I get this error message : Error: '1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9'

By the way, I don’t know why it is said "line 9" since this variable starts at line 132 of my program.

I have also assumed that this variable causes this error since, when I remove it, the error disappears, but I may be wrong.

Thank you for your time 🙂

>Solution :

I suppose you missed ) at the end of the statement. Try:

create_recipe_ingredient_table = """
CREATE TABLE IF NOT EXISTS recipe_ingredient (
    recipe_ingredient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount INT,
    recipe_id INT,
    ingredient_id INT,
    measure_id INT,
    FOREIGN KEY (recipe_id) REFERENCES recipe(recipe_id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id),
    FOREIGN KEY (measure_id) REFERENCES measure(measure_id));
    """