sqlite3 mit 'ON DELETE CASCADE' funktioniert nicht wie erwartet

[Judge]

Hallo zusammen,

ich habe folgende sqlite DB erstellt:

Code: Alles auswählen

CREATE TABLE IF NOT EXISTS game_group (
    group_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL,
    name text UNIQUE NOT NULL,
    created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS players (
    player_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL,
    name text UNIQUE NOT NULL,
    created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS game_group_members (
    added_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    group_id integer,
    player_id integer,
    PRIMARY KEY (group_id, player_id),
    CONSTRAINT fk_game_group
      FOREIGN KEY (group_id) REFERENCES game_group(group_id)
      ON DELETE CASCADE,
    CONSTRAINT fk_players
      FOREIGN KEY (player_id) REFERENCES players(player_id)
      ON DELETE CASCADE
);

Meine Absicht ist, das wenn ich einen Spieler aus "players" oder eine Gruppe aus "game_group" lösche, alle entsprechenden Datensätze hierdurch auch aus der Tabelle "game_group_members" gelöscht werden. Das klappt jedoch nicht.

Ich lege die DB und Test-Einträge wie folgt an:

Code: Alles auswählen

conn = sqlite3.connect('test_db.sqlite')
cur = conn.cursor()
cur.executescript(init_sql)

# Create a player
query = "INSERT INTO players (name) VALUES (?)"
cur.execute(query, ('TestUser',))

# Create a group
query = "INSERT INTO game_group (name) VALUES (?)"
cur.execute(query, ('TestGroup',))

# Display tables
query = "SELECT * FROM players"
cur.execute(query).fetchall()   # Liefert: [(1, 'TestUser', '2020-12-18 08:35:09')]

query = "SELECT * FROM game_group"
cur.execute(query).fetchall()   # Liefert: [(1, 'TestGroup', '2020-12-18 08:42:43')]

# Add the player to the group
query = "INSERT INTO game_group_members (group_id, player_id) VALUES (?, ?)"
cur.execute(query, (1, 1))

# Show the group assignment
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall()   # Liefert: [('2020-12-18 08:48:15', 1, 1)]

# Delete the player
query = "DELETE FROM players WHERE player_id = ?"
cur.execute(query, (1,))
conn.commit()
# Show table 'players'
query = "SELECT * FROM players"
cur.execute(query).fetchall()   # Liefert: []

# Show the group assignments
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall()   # Liefert: [('2020-12-18 08:48:15', 1, 1)]

Was mache oder verstehe ich hier falsch? Warum ist durch das löschen des Spielers 1 nicht auch der entsprechende Datensatz aus game_group_members verschwunden?

[Judge]

... OK, teilweise habe ich es mir selbst beantworten können: Alles an dem was ich geschrieben habe scheint korrekt zu sein. Jedoch scheint das foreign key - handling in sqlite3 nicht standardmäßig aktiviert zu sein. Wenn man nach erstellen der Connection folgendes ausführt verhält sich das ON DELETE CASCADE wie erwartet:

Code: Alles auswählen

conn.execute("PRAGMA foreign_keys = ON;")

Kommt mir jedoch komisch vor; warum muss man eine so grundlegende SQL Funktion extra aktivieren? Das "schmeckt" für mich ähnlich als müsste ich auch die Prüfung nach Unique keys extra aktivieren, weil es noch lange nicht gemacht wird, nur weil ich meine Tabelle entsprechend anlege; das wird aber ja auch als feste, selbstverständlich zu erledigende Aufgabe angenommen.
Und man bekommt auch nichtmal eine Warnung das es nicht ausgeführt wird ... ?

[__blackjack__]

@Judge: Sqlite3 schluckt eine ganze Menge Standard-SQL und ignoriert das einfach und/oder verhält sich anders als erwartet. Ich würde das auch nicht unbedingt als grundlegende SQL-Funktion ansehen. Also Fremdschlüssel als Konzept und Information für den Nutzer im relationalen Modell ja, aber das die DB da irgendwas mit macht, würde ich von einem „SQL Lite“-Produkt im Gegensatz zu einem „SQL regular“-Produkt nicht zwingend erwarten. Irgendwo muss das „lite“ im Namen sich ja in Abstrichen niederschlagen. Und bei einer DB die nicht mal die Typen in den Spalten prüft, sollte einen nichts überraschen.