Foreign keys and ON DELETE CASCADE not working properly

[tesa-klebeband]

I am working on a project that utilizes foreign keys and their ON DELETE CASCADE property in order to remove unneeded entries from different tables. However, this function doesn't seem to work properly on my side as if I delete the row and thus also the column that the foreign key points to nothing changes and the row containing the foreign key still exists. I wrote a small snipped of code to test this behavior on another ESP32.

#include <Arduino.h>
#include <sqlite3.h>
#include <SPIFFS.h>
#include <FS.h>

void setup() {
  Serial.begin(115200);

  sqlite3 *db;

  if (!SPIFFS.begin(true)) {
    Serial.println("An Error has occurred while mounting SPIFFS");
    return;
  }

  SPIFFS.remove("/test.db");

  sqlite3_initialize();

  sqlite3_open("/spiffs/test.db", &db);

  if (sqlite3_exec(db, "PRAGMA foreign_keys = ON;", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while enabling foreign keys: %s\n", sqlite3_errmsg(db));
    while(1);
  }
  
  if (sqlite3_exec(db, "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while creating the table: %s\n", sqlite3_errmsg(db));
    while(1);
  }

  if (sqlite3_exec(db, "CREATE TABLE ref (test_id INTEGER, some_value INTEGER, FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE);", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while creating the table: %s\n", sqlite3_errmsg(db));
    while(1);
  }

  if (sqlite3_exec(db, "INSERT INTO test (name) VALUES ('test');", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while inserting data: %s\n", sqlite3_errmsg(db));
    while(1);
  }

  if (sqlite3_exec(db, "INSERT INTO ref (test_id, some_value) VALUES (1, 123);", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while inserting data: %s\n", sqlite3_errmsg(db));
    while(1);
  }

  sqlite3_stmt *stmt;
  if (sqlite3_prepare_v2(db, "SELECT * FROM ref;", -1, &stmt, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while preparing the statement: %s\n", sqlite3_errmsg(db));
    while(1);
  }
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    Serial.printf("test_id: %d, some_value: %d\n", sqlite3_column_int(stmt, 0), sqlite3_column_int(stmt, 1));
  }
  sqlite3_finalize(stmt);

  if (sqlite3_exec(db, "DELETE FROM test WHERE id = 1;", 0, 0, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while deleting data: %s\n", sqlite3_errmsg(db));
    while(1);
  }

  if (sqlite3_prepare_v2(db, "SELECT * FROM ref;", -1, &stmt, 0) != SQLITE_OK) {
    Serial.printf("An error occurred while preparing the statement: %s\n", sqlite3_errmsg(db));
    while(1);
  }
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    Serial.printf("test_id: %d, some_value: %d\n", sqlite3_column_int(stmt, 0), sqlite3_column_int(stmt, 1));
  }
  sqlite3_finalize(stmt);
}

void loop() {
}

I am expecting this code to only output the rows one time when no delete statement has been executed, but i am getting the same row after the delete statement too. It would be great if this issue was eliminated as I have to currently run select statements and then delete statements which is not really practical. Thanks