Foreign Key constraint is not working properly on SQLite

Question

import sqlite3
# Constructing the database
def create_database():
    #Create a database or connect to a database
    conn = sqlite3.connect('games_database.db')
    conn.execute("PRAGMA foreign_keys = 1") #Turns ON foreign key constraints
    #Create cursor
    c = conn.cursor()
    #Create tables
    #Create game_publisher table1
    c.execute("""CREATE TABLE IF NOT EXISTS game_publisher(
            gp_id INTEGER NOT NULL,
            gp_name TEXT NOT NULL,
            gp_year_established INTEGER NOT NULL,
            PRIMARY KEY(gp_id))
            """)
    #Create game_genre table2
    c.execute("""CREATE TABLE IF NOT EXISTS game_genre(
            genre_id INTEGER NOT NULL,
            genre_name TEXT NOT NULL,
            PRIMARY KEY(genre_id))
            """)
    #Create platform table3 (Original Platform)
    c.execute("""CREATE TABLE IF NOT EXISTS platform(
            platform_id INTEGER NOT NULL,
            platform_name TEXT NOT NULL,
            PRIMARY KEY(platform_id))
            """)
    #Create games table4
    c.execute("""CREATE TABLE IF NOT EXISTS games(
            g_id INTEGER NOT NULL,
            g_name TEXT NOT NULL,
            g_genre INTEGER NOT NULL DEFAULT 0,
            g_pub INTEGER NOT NULL DEFAULT 0,
            year_released INTEGER NOT NULL,
            original_platform INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY(g_id),
            FOREIGN KEY(g_genre) REFERENCES game_genre(genre_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
            FOREIGN KEY(g_pub) REFERENCES game_publisher(gp_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
            FOREIGN KEY(original_platform) REFERENCES platform(platform_id) ON DELETE SET DEFAULT ON UPDATE CASCADE)
            """)
    
    #Commit changes
    conn.commit()
    #Close connection
    conn.close()

I've tried looking up online and found that SQLite by default has the Foreign key constraint set to OFF. I found that I had to use this "conn.execute("PRAGMA foreign_keys = 1")" to turn it ON.

When I try to delete a game_publisher, and then view the games table, I don't see the games table update the game_publisher ID to 0.

Do I have to turn ON the foreign key constraint every time I make a connection to the database?

user_10498828 · Answer

The problem is this:

ON DELETE SET DEFAULT

with the definition of the foreign keys in games, because the default value for all of them is defined to be 0 which (I suspect) does not correspond to a valid id of the corresponding referenced tables.

What you can do is define the foreign keys as nullable and with ON DELETE SET NULL like this:

CREATE TABLE IF NOT EXISTS games (
  g_id INTEGER NOT NULL,
  g_name TEXT NOT NULL,
  g_genre INTEGER, -- nullable
  g_pub INTEGER, -- nullable
  year_released INTEGER NOT NULL,
  original_platform INTEGER, -- nullable
  PRIMARY KEY(g_id),
  FOREIGN KEY(g_genre) REFERENCES game_genre(genre_id) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(g_pub) REFERENCES game_publisher(gp_id) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(original_platform) REFERENCES platform(platform_id) ON DELETE SET NULL ON UPDATE CASCADE
)

Now when you delete a row from a parent table, the child key value will be set to NULL, which is allowed.

Finally, for the question:

Do I have to turn ON the foreign key constraint every time I make a connection to the database?

the answer is yes, if you are going to use operations involving the integerity of the foreign keys, like the insertion of a new row or update a row in the child table games or the modification of rows (deletions or updates) of the parent tables.

Foreign Key constraint is not working properly on SQLite

1 Answers