SQLite In-memory Cascade Delete not working in tests

[kimimaru4000]

Backend

SQLite

Diesel version

2.3.2

Diesel Features

"sqlite", "returning_clauses_for_sqlite_3_35", "time", "chrono"

Operating System Version

Arch Linux

Third party libraries

diesel_migrations = "2.3.0", time="0.3.44"

What do you want to do?

A UserStats table relies on a user_id foreign key from a Users table. If the associated User is deleted, that UserStats object should be deleted as well since a cascade delete is defined for UserStats.

In unit tests with an in-memory database (via ":memory:"), deleting the User then looking up the UserStats table returns an Ok result instead of an Err.

Compile time error

No response

What code do you already have?

#[test]
fn remove_user_should_also_remove_stats()
{
    let mut db = load_migrations();

    write_user_stats_with_db(&mut db);

    let delete_res = diesel::delete(
        users::table.filter(users::id.eq(0))
    )
    .execute(&mut db);
        
    assert!(delete_res.is_ok());

    // Check the UserStats are also deleted
    let found_stats = userstats::table
    .find(1)
    .first::<UserStats>(&mut db)
    .optional();

    assert!(found_stats.unwrap().is_none()); // <---- failing
}

== SQLITE MIGRATIONS ==

CREATE TABLE `userstats`(
	`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`user_id` INTEGER NOT NULL,
	CONSTRAINT `fk_userstats_users_userid` FOREIGN KEY(`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
);

### Additional details

_No response_

### Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) and the [discussion forum](https://github.com/diesel-rs/diesel/discussions) for similar possible closed issues.

[weiznich]

Given that you can reproduce the same behaviour by using the sqlite3 binary and execute the same queries there manually, this is expected behaviour from diesels side. We don't hide any database specific behaviour like that.

Now you can change the default SQLite behaviour by tweaking SQLite settings like PRAGMA foreign_keys to make SQLite behave more like you expect.