I am attempting to make a database in Python using SQLite3, working around foreign keys doesn't seem to be going to well, I keep getting error messages and i am fully aware my poor formatting is somewhat of a contributor.
This is all of the code
import sqlite3
conn = sqlite3.connect("POS SYSTEM.db")
c = conn.cursor()
###################################################################
Suppliers = "CREATE TABLE IF NOT EXISTS Suppliers ( Supplier_ID INTEGER PRIMARY KEY AUTOINCREMENT, Supplier_Name TEXT NOT NULL)"
c.execute(Suppliers)
Stock_Items = "CREATE TABLE IF NOT EXISTS Stock_Items ( Stock_ID INTEGER PRIMARY KEY AUTOINCREMENT, Name_Of_Stock_Item TEXT NOT NULL, Cost_Of_Bulk_Item INTEGER NOT NULL, Supplier_ID INTEGER, FOREIGN KEY (Supplier_ID) REFERENCES Suppliers(Supplier_ID))"
c.execute(Stock_Items)
Current_Stock_List = "CREATE TABLE IF NOT EXISTS Current_Stock_List ( Current_Stock_List_ID INTEGER PRIMARY KEY AUTOINCREMENT, Name_Of_CStock_Item TEXT NOT NULL, Spoil_Date DATE NOT NULL, Portions_Per_Bulk_Item INTEGER NOT NULL, Stock_Order_ID INTEGER, Stock_ID INTEGER, FOREIGN KEY (Stock_ID) REFERENCES Stock_Items(Stock_ID))"
c.execute(Current_Stock_List)
Menu_Items = "CREATE TABLE IF NOT EXISTS Menu_Items ( Menu_Item_ID INTEGER PRIMARY KEY AUTOINCREMENT, Menu_Name TEXT NOT NULL)"
c.execute(Menu_Items)
Ingredients = "CREATE TABLE IF NOT EXISTS Ingredients ( INGMenu_Item_Name TEXT NOT NULL, Ingredient_Name TEXT NOT NULL, Stock_ING_ID INTEGER, Stock_ID INTEGER, FOREIGN KEY (Stock_ID) REFERENCES Stock_Items(Stock_ID)), Menu_Item_ID INTEGER, FOREIGN KEY (Menu_Item_ID) REFERENCES Menu_Items(Menu_Item_ID))"
c.execute(Ingredients)
This seems to be the problem statement I just can't figure out what's wrong with it, and if i could streamline the process in the future.
Ingredients = "CREATE TABLE IF NOT EXISTS Ingredients ( INGMenu_Item_Name TEXT NOT NULL,
Ingredient_Name TEXT NOT NULL, Stock_ING_ID INTEGER,
Stock_ID INTEGER, FOREIGN KEY (Stock_ID) REFERENCES Stock_Items(Stock_ID)),
Menu_Item_ID INTEGER, FOREIGN KEY (Menu_Item_ID) REFERENCES Menu_Items(Menu_Item_ID))"
c.execute(Ingredients)
Running my code presents me with this error:
Traceback (most recent call last): line 23, in <module> c.execute(Ingredients) sqlite3.OperationalError: near ",": syntax error
Fiddling with the code I found that declaring foreign keys and then other columns afterwards came with an error message.
Stock_Items = "CREATE TABLE IF NOT EXISTS Stock_Items ( Stock_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Name_Of_Stock_Item TEXT NOT NULL,
Cost_Of_Bulk_Item INTEGER NOT NULL,
Supplier_ID INTEGER, FOREIGN KEY (Supplier_ID) REFERENCES Suppliers(Supplier_ID))"
c.execute(Stock_Items)
This is that error message which is of course very similar to my main issue i just can't see the problem:
Traceback (most recent call last): line 17, in <module> c.execute(Current_Stock_List) sqlite3.OperationalError: near ",": syntax error
Sorry if this is obvious or a waste of time, I'm still new and learning so thanks for the help. :)
