Hi everyone,
I'm new to SQLite; I am using it in my Python scripts as `sqlite3` build-in module.
I have created a minimal DB schema, which has 3 tables:
1. Players
2. Groups
3. Group Assignments
The SQL looks like this:
```sql
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
);
```
I am creating a new database and load this script into it like this:
```python
import sqlite3
conn = sqlite3.connect('test_db.sqlite')
cur = conn.cursor()
cur.executescript(init_sql)
```
`init_sql` contains the previously shown SQL code.
I then create a player named `TestUser`:
```python
query = "INSERT INTO players (name) VALUES (?)"
cur.execute(query, ('TestUser',))
```
After that, I create a group named `TestGroup`
```python
query = "INSERT INTO game_group (name) VALUES (?)"
cur.execute(query, ('TestGroup',))
```
The result looks like this:
```python
query = "SELECT * FROM players"
cur.execute(query).fetchall()
-> [(1, 'TestUser', '2020-12-18 08:35:09')]
query = "SELECT * FROM game_group"
cur.execute(query).fetchall()
-> [(1, 'TestGroup', '2020-12-18 08:42:43')]
```
Next, I assign the player to that group
```python
query = "INSERT INTO game_group_members (group_id, player_id) VALUES (?, ?)"
cur.execute(query, (1, 1))
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall()
-> [('2020-12-18 08:48:15', 1, 1)]
```
My expectation now is that when deleting player '1' from table 'players', the group assignment for player '1' in 'game_group_members' should be deleted as well, due to the 'ON DELETE CASCADE' constraint.
```python
query = "DELETE FROM players WHERE player_id = ?"
cur.execute(query, (1,))
conn.commit()
query = "SELECT * FROM players"
cur.execute(query).fetchall()
-> []
```
As expected, the table `players` is now empty.
But the entry in `game_group_members` still exists:
```python
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall()
-> [('2020-12-18 08:48:15', 1, 1)]
```
What am I doing or understanding wrong here, please?
Thanks for your help!
Best regards,
Judge