Using Foreign Keys In Sqlite3 For Python

I'm writing a program that creates a sqlite3 database through python. I have one table of Authors (AuthorID, Name) and a second table of books (BookID, Title, AuthorID) I've create

Solution 1:

The PRAGMA foreign_keys setting applies to a connection, so you should execute it immediately after calling sqlite3.connect().

Please note that foreign key constraints work only inside the same database; you should put both tables into the same file.

Solution 2:

So to do what you want to do you need to create one database file with 2 tables.

Example:

conn=sqlite3.connect("clientdatabase.db")
conn.execute("PRAGMA foreign_keys = 1")
cur=conn.cursor()

# Create2 tables if they don't exist: Clients and Work_Done
cur.execute('''CREATETABLE IF NOTEXISTS Clients
(CID INTEGERPRIMARY KEY,
First_Name  TEXT    NOTNULL,
Last_Name       TEXT,
Business_Name   TEXT,
Phone           TEXT,
Address         TEXT,
City            TEXT,
Notes           TEXT,
Active_Status   TEXT    NOTNULL)''')      

cur.execute('''CREATETABLE IF NOTEXISTS Work_Done
(ID INTEGERPRIMARY KEY,
Date            TEXT    NOTNULL,
Onsite_Contact  TEXT,
Work_Done       TEXT    NOTNULL,
Parts_Installed TEXT,
Next_Steps      TEXT,
CID             INT,
FOREIGN KEY (CID) REFERENCES CLIENTS (CID))''')
conn.commit()

Note that both tables are in the same database and you add the line after connection and before the cursor object.

Hope this helps.

Solution 3:

Also note that if there is an active transaction, the PRAGMA foreign_keys does not work. There is no error message if you try to do so but foreign keys will still be turned off.

If you have problems with foreign keys even after using the pragma, it may be worth an attempt to execute COMMIT once before using it.