Purging user and SQLite3 foreign keys enforcement

[dgvirtual]

I am using SQLite3 db with CI4 and Shield. When I purge user from db, only the entry in the users table is deleted, while the other table entries related to the same user remain. This is because SQLite3 by default does not enforece foreign keys, so the change in users table does not cascade to the other related tables. However, I cannot seem to find a way to force that behaviour. The DB is created using the migration procedure, and it does include foreign keys like

CREATE TABLE IF NOT EXISTS "auth_identities" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,

"updated_at" datetime,
CONSTRAINT "auth_identities_user_id_foreign" FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

I have entered a piece of code

    db_connect()->simpleQuery('PRAGMA foreign_keys=ON'); 

Into the BaseController.php initController function, which should enforce the foreign keys, but it does not do the trick.

Maybe anyone happens to understand why it might be so?

[dgvirtual]

OK, so I have realized that the app BaseController.php was not run in my app case (it is the default Bonfire2 install). When I put it in another place and added another simple query after it that deletes one of the users, it got executed and the user was deleted with all dependent entries.

That far – good. I am still not sure where to put that code so that it covers my whole Bonfire2 app, but that is perhaps not the problem with Shield.

However, I would suggest that, since Codeigniter 4 officially supports SQLite3, the Shield should perhaps enforce foreign keys by default in case the database is SQLite3. Another way would be to refactor code so that the code is not dependent on the database enforcing foreign key constraints.

[michalsn]

There is a config variable for this. Please see https://www.codeigniter.com/user_guide/database/configuration.html#explanation-of-values - the last option in the table: foreignKeys.

[dgvirtual]

Oh, I see, it is somehow omitted from the default db array and available on testing in the config file...

It did work after I added the line to the $default database array in Config/Database.php (and the

database.default.foreignKeys = true

to .env, though the latter is not necessary I guess.

Thanks @michalsn