How do I create a foreign key in sqlite3 python

Question

I can't get my program to use a foreign key for some unbeknown reason. I have read other questions related to this but cant find an answer that works. I tried the PRAGMA foreign_keys = ON, but instead i get a syntax error for "foreign_keys". I also tried removing this and running the program again and instead got an error saying the column I'm trying to create doesn't exist

import sqlite3
import os
import random
from tkinter import *
import uuid
PRAGMA foreign_keys = ON

conn = sqlite3.connect('MyComputerScience.db')
c = conn.cursor() 
c.execute("""CREATE TABLE IF NOT EXISTS users (
            UserID text PRIMARY KEY,
            FName text,
            SName text,
            username text,
            password varchar,
            userType text)""")

c.execute("""CREATE TABLE IF NOT EXISTS classes (
            ClassID PRIMARY KEY,
            FOREIGN KEY (User) REFERENCES users(UserID)))""")





 FOREIGN KEY (User) REFERENCES users(UserID)))""")
sqlite3.OperationalError: unknown column "User" in foreign key definition

The PRAGMA line should not be in the Python code as it is not valid Python (hence the SyntaxError). Instead, execute that line immediately after creating the connection (conn.execute("PRAGMA foreign_keys = on")). See this answer: stackoverflow.com/a/29427284/1441112 — goodmami
– goodmami, Commented Jul 3, 2020 at 9:32

Tony · Accepted Answer · 2019-08-20 12:57:22Z

1

You need to create the fields in the table before using them as a foreign key.

As you did not add a field User to your table, you got the error:

unknown column "User" in foreign key definition

To fix the error, add User to your table definition

c.execute("""CREATE TABLE IF NOT EXISTS classes (
            ClassID PRIMARY KEY,
            User text,
            FOREIGN KEY (User) REFERENCES users(UserID)))""")

However, I would question the design. The table classes would only be able to relate one user per class. Is that what you want to do?

edited Aug 20, 2019 at 12:57

answered Aug 20, 2019 at 12:10

Tony

10.4k3 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kostas Charitidis · Accepted Answer · 2019-08-20 12:05:39Z

0

c.execute("""CREATE TABLE IF NOT EXISTS classes (
            ClassID PRIMARY KEY,
            FOREIGN KEY (ClassID) REFERENCES users(UserID)))""")

That's the correct syntax but this way you set as foreign key your primary key. Maybe you wanted to use another field.

answered Aug 20, 2019 at 12:05

Kostas Charitidis

3,1231 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

How do I create a foreign key in sqlite3 python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related