What's wrong with my python + sqlite3 code in creating tables?

Question

I'm trying to create a database with several tables connecting to each other using foreign keys using sqlite3, and I'm writing in python.

Here is my code:

db = sqlite3.connect("PHLC.db")
cur = db.cursor()
# ############################
# delete original table if exist
# drop from the end (foreign key issue)
cur.execute("drop table if exists measurement")
cur.execute("drop table if exists mouse")
cur.execute("drop table if exists drug")
cur.execute("drop table if exists batch")
cur.execute("drop table if exists phlc")
# ############################
# create table
# ############################
# 1. phlc
cur.execute(
 """
 CREATE TABLE phlc (
 phlc_id INTEGER NOT NULL PRIMARY KEY,
 cancer VARCHAR(30) NOT NULL,
 histology VARCHAR(60) NOT NULL
 )
 """
)
# 2. batch
cur.execute(
 """
 CREATE TABLE batch (
 batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
 phlc_id INTEGER NOT NULL,
 FOREIGN KEY (phlc_id) REFERENCES phlc (phlc_id),
 batch_number INTEGER NOT NULL
 )
 """
)
# 3. drug
cur.execute(
 """
 CREATE TABLE drug (
 drug_id INTEGER PRIMARY KEY AUTOINCREMENT,
 drug_name VARCHAR(30) NOT NULL,
 batch_id INTEGER NOT NULL,
 FOREIGN KEY (batch_id) REFERENCES batch (batch_id)
 )
 """
)
# 4. mouse
cur.execute(
 """
 CREATE TABLE mouse (
 mouse_id INTEGER PRIMARY KEY AUTOINCREMENT,
 drug_id INTEGER NOT NULL,
 FOREIGN KEY (drug_id) REFERENCES drug (drug_id)
 )
 """
) 
# 5. measurement
cur.execute(
 """
 CREATE TABLE measurement (
 measurement_index INTEGER PRIMARY KEY AUTOINCREMENT,
 mouse_id INTEGER NOT NULL,
 FOREIGN KEY (mouse_id) REFERENCES mouse (mouse_id),
 day INTEGER NOT NULL,
 tumor_volume FLOAT NOT NULL,
 comment VARCHAR(255) NULL
 )
 """
) 
db.commit()
db.close()

The error I'm getting is at the batch table:

sqlite3.OperationalError: near "batch_number": syntax error

Can someone point out the problem with the code? (It worked fine with MySQL..)

• Sirac: I'm just guessing, but could it be that you have to define all columns Before the key-definition?
• Yilun Zhang: I see what you mean, let me try
• Sirac: I meant all, I also just tested the code and it works if you put the key definition at the end of the statement. The more i think of it, the more I think that you only have to put non-primary keys at the end of the statement.

Answer 1

According to the documentation, any table constraints must come after all column definitions:

CREATE TABLE batch (
 batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
 phlc_id INTEGER NOT NULL,
 batch_number INTEGER NOT NULL,
 FOREIGN KEY (phlc_id) REFERENCES phlc (phlc_id)
)

Alternatively, make the foreign key declaration a column constraint:

CREATE TABLE batch (
 batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
 phlc_id INTEGER NOT NULL REFERENCES phlc (phlc_id),
 batch_number INTEGER NOT NULL
)