Bug: SQLite3 foreign key does not work by default

[kenjis]

PHP Version

8.0

CodeIgniter4 Version

develop

CodeIgniter4 Installation Method

Git

Which operating systems have you tested for this bug?

macOS

Which server did you use?

cli-server (PHP built-in webserver)

Database

SQLite3

What happened?

Can insert a record that has no foreign key record.

Steps to Reproduce

<?php

namespace App\Controllers;

use Config\Database;

class Home extends BaseController
{
    public function index()
    {
        $group = [
            'database' => 'sqlite3.db',
            'DBDriver' => 'SQLite3',
            'DBPrefix' => 'db_',
            'DBDebug'  => (ENVIRONMENT !== 'production'),
        ];
        $db = \Config\Database::connect($group);

        $this->forge = Database::forge($group);

        $this->createTableAuthUsers();
        $this->createTableAuthIdentities();

        $prefix = $db->getPrefix();

        $sql = "INSERT INTO {$prefix}auth_identities"
            . ' (user_id, type, secret, created_at)'
            . " VALUES (2, 'email_2fa', '479123', '2022-05-18 23:01:46')";
        $db->simpleQuery($sql);

        $query = $db->query("select * from {$prefix}auth_users");
        var_dump($query->getResult());
        $query = $db->query("select * from {$prefix}auth_identities");
        var_dump($query->getResult());
    }

    private function createTableAuthUsers()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'int',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'username' => [
                'type'       => 'varchar',
                'constraint' => 30,
                'null'       => true,
            ],
            'active' => [
                'type'       => 'tinyint',
                'constraint' => 1,
                'null'       => 0,
                'default'    => 0,
            ],
            'last_active' => [
                'type' => 'datetime',
                'null' => true,
            ],
            'created_at' => [
                'type' => 'datetime',
                'null' => true,
            ],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('username');
        $this->forge->createTable('auth_users', true);
    }

    private function createTableAuthIdentities()
    {
        $this->forge->addField([
            'id'           => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id'      => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'type'         => ['type' => 'varchar', 'constraint' => 255],
            'secret'       => ['type' => 'varchar', 'constraint' => 255],
            'secret2'      => ['type' => 'varchar', 'constraint' => 255, 'null' => true],
            'expires'      => ['type' => 'datetime', 'null' => true],
            'last_used_at' => ['type' => 'datetime', 'null' => true],
            'created_at'   => ['type' => 'datetime', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['type', 'secret']);
        $this->forge->addKey('user_id');
        $this->forge->addForeignKey('user_id', 'auth_users', 'id', '', 'CASCADE');
        $this->forge->createTable('auth_identities', true);
    }
}

Navigate to http://localhost:8080/

.../CodeIgniter4/app/Controllers/Home.php:32:
array (size=0)
  empty

.../CodeIgniter4/app/Controllers/Home.php:34:
array (size=1)
  0 => 
    object(stdClass)[74]
      public 'id' => int 1
      public 'user_id' => int 2
      public 'type' => string 'email_2fa' (length=9)
      public 'secret' => string '479123' (length=6)
      public 'secret2' => null
      public 'expires' => null
      public 'last_used_at' => null
      public 'created_at' => string '2022-05-18 23:01:46' (length=19)

Expected Output

ErrorException

SQLite3::exec(): FOREIGN KEY constraint failed

Anything else?

This is the default behavior of SQLite3.

As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite. The default setting for foreign key enforcement can be specified at compile-time using the SQLITE_DEFAULT_FOREIGN_KEYS preprocessor macro. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting.
https://www.sqlite.org/pragma.html#pragma_foreign_keys

But dropTable() executes PRAGMA foreign_keys = ON,
and after that foreign key constraint works.

If you add the following code before creating the table, you will see ErrorException.

$this->forge->dropTable('auth_users', true);