Can anyone help me with this SQLite 3 Python Operational Error?

Question

I am trying to create a database program using SQLite3 and Tkinter. It is for my Computer Science A-Level Course. I am getting this error whenever I try to run the following code:

Exception has occurred: OperationalError near "BranchID": syntax error File "\Code\tables.py", line 14, in cursor.execute('''

import sqlite3
with sqlite3.connect("LeeOpt.db") as db:
 cursor = db.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS Branches(
BranchID INTEGER PRIMARY KEY,
Town VARCHAR(30) NOT NULL,
Postcode VARCHAR(8) NOT NULL,
Email VARCHAR(30) NOT NULL,
Telephone VARCHAR(15) NOT NULL);
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS Customer(
CustomerID INTEGER PRIMARY KEY,
BranchID INTEGER NOT NULL,
Name VARCHAR(20) NOT NULL,
Surname VARCHAR(30) NOT NULL,
DateOfBirth NOT NULL,
Town VARCHAR(30) NOT NULL,
Postcode VARCHAR(7) NOT NULL,
EmailAddress VARCHAR(30) NOT NULL,
TelephoneNo VARCHAR(12) NOT NULL,
MedicalConditions TEXT,
FOREIGN KEY BranchID REFERENCES Branches(BranchID));
''')

I am struggling to understand what has went wrong. Could anyone help me solve this problem? Thanks.

• Maximilian Burszley: Is your indentation like that in the file or is your question wrong?

Answer 1

I think the error was more with indentation, try this:

cursor.execute('''
CREATE TABLE IF NOT EXISTS Branches(
BranchID INTEGER PRIMARY KEY,
Town VARCHAR(30) NOT NULL,
Postcode VARCHAR(8) NOT NULL,
Email VARCHAR(30) NOT NULL,
Telephone VARCHAR(15) NOT NULL);''') # moved the triple quote together with the same line
cursor.execute('''
CREATE TABLE IF NOT EXISTS Customer(
CustomerID INTEGER PRIMARY KEY,
BranchID INTEGER NOT NULL,
Name VARCHAR(20) NOT NULL,
Surname VARCHAR(30) NOT NULL,
DateOfBirth NOT NULL,
Town VARCHAR(30) NOT NULL,
Postcode VARCHAR(7) NOT NULL,
EmailAddress VARCHAR(30) NOT NULL,
TelephoneNo VARCHAR(12) NOT NULL,
MedicalConditions TEXT,
FOREIGN KEY(BranchID) REFERENCES Branches(BranchID));''') #made proper syntax changes here 

Just googling syntax for FOREIGN KEY got me this:

FOREIGN KEY(columnname) REFERENCES tablename(columnname)

So you forgot to enclose the BranchID in ().