python - 外键约束在 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()

我尝试在线查找，发现 SQLite 默认情况下将外键约束设置为 OFF。
我发现我必须使用这个“conn.execute(“PRAGMA foreign_keys = 1”)”来打开它。
当我尝试删除一个 game_publisher，然后查看游戏表时，我没有看到游戏表将 game_publisher ID 更新为 0。
每次连接到数据库时是否都必须打开外键约束？

Answer 1

问题是这样的:

ON DELETE SET DEFAULT

与 games 中的外键定义，因为所有这些的默认值都定义为 0其中(我怀疑)与相应引用表的有效 ID 不对应。
您可以做的是将外键定义为可为空并使用 ON DELETE SET NULL像这样:

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
)

现在，当您从父表中删除一行时，子键值将设置为 NULL ，这是允许的。
最后，对于这个问题:

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

答案是肯定的，如果您要使用涉及外键整数性的操作，例如插入新行或更新子表中的行 games或修改父表的行(删除或更新)。