Step-by-Step Guide: CodeIgniter 4 Adding Foreign Key in Migration

In a database, a foreign key is a field that references another table.

They keep track of related records and which table they exist in. They also let you know what record they relate to, which means updating them is simple and quick.

In this tutorial, I show how you can add a foreign key while creating table using migration in CodeIgniter 4.

Step-by-Step Guide: CodeIgniter 4 Adding Foreign Key in Migration

Table of Content

Update Database Configuration
Using Migration Create Tables and add Foreign key
Create Models
Conclusion

1. Update Database Configuration

Open .env file which is available at the project root.

NOTE – If dot (.) not added at the start then rename the file to .env.

Remove # from start of database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
Update the configuration and save it.

database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi

2. Using Migration Create Tables and add Foreign key

I am creating 2 tables –

departments
employees

Adding foreign key depart_id field on the employees table.

departments Table

Create departments table –

php spark migrate:create create_departments_table

Navigate to app/Database/Migrations/ folder.
Find PHP file that ends with CreateDepartmentsTable.php and open it.
In the up() method define table structure.
Using the down() method delete departments table that calls when undoing migration.

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateDepartmentsTable extends Migration
{
     public function up() {
          $this->forge->addField([
             'id' => [
                  'type' => 'INT',
                  'constraint' => 5,
                  'unsigned' => true,
                  'auto_increment' => true,
             ],
             'name' => [
                  'type' => 'VARCHAR',
                  'constraint' => '100',
             ]
          ]);
          $this->forge->addKey('id', true);
          $this->forge->createTable('departments');
     }

     public function down() {
         $this->forge->dropTable('departments');
     }
}

employees Table

Create employees table –

php spark migrate:create create_employees_table

Navigate to app/Database/Migrations/ folder and open PHP file that ends with CreateEmployeesTable.php.
In this table creating id, depart_id, and name fields.

Add foreign key –

Here, using depart_id field to define foreign key.
Call $this->forge->addForeignKey() method to set foreign key.
In the method pass 5 parameters –
1. depart_id – Foreign key field name.
2. departments – Parent table name.
3. id – Primary key or unique field name of the parent table that needs to link.
4. CASCADE – Delete matching records when the delete query executes in the parent table.
5. CASCADE – Update matching records when the update query executes in the parent table.

$this->forge->addForeignKey('depart_id', 'departments', 'id', 'CASCADE', 'CASCADE');

Specify employees table in the down() method that calls when undoing migration.

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateEmployeesTable extends Migration
{
    public function up() {
       $this->forge->addField([
           'id' => [
               'type' => 'INT',
               'constraint' => 5,
               'unsigned' => true,
               'auto_increment' => true,
           ],
           'depart_id' => [
               'type' => 'INT',
               'constraint' => 5,
               'unsigned' => true,
           ],
           'name' => [
               'type' => 'VARCHAR',
               'constraint' => '100',
           ]
       ]);

       $this->forge->addKey('id', true);
       $this->forge->addForeignKey('depart_id', 'departments', 'id', 'CASCADE', 'CASCADE');
       $this->forge->createTable('employees');

    }

    public function down() {
       $this->forge->dropTable('employees');
    }
}

Run the migration –

php spark migrate

3. Create Models

Create 2 models –

Departments
Employees

Departments Model

Create Departments Model –

php spark make:model Departments

Open app/Models/Departments.php file.
In $allowedFields Array specify field names – ['name'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

class Departments extends Model
{
    protected $DBGroup = 'default';
    protected $table = 'departments';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;
    protected $insertID = 0;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    protected $protectFields = true;
    protected $allowedFields = ['name'];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat = 'datetime';
    protected $createdField = 'created_at';
    protected $updatedField = 'updated_at';
    protected $deletedField = 'deleted_at';

    // Validation
    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert = [];
    protected $afterInsert = [];
    protected $beforeUpdate = [];
    protected $afterUpdate = [];
    protected $beforeFind = [];
    protected $afterFind = [];
    protected $beforeDelete = [];
    protected $afterDelete = [];
}

Employees Model

Create Employees Model –

php spark make:model Employees

Open app/Models/Employees.php file.
In $allowedFields Array specify field names – ['depart_id','name'] that can be set during insert and update.

Completed Code

<?php

namespace App\Models;

use CodeIgniter\Model;

class Employees extends Model
{
     protected $DBGroup = 'default';
     protected $table = 'employees';
     protected $primaryKey = 'id';
     protected $useAutoIncrement = true;
     protected $insertID = 0;
     protected $returnType = 'array';
     protected $useSoftDeletes = false;
     protected $protectFields = true;
     protected $allowedFields = ['depart_id','name'];

     // Dates
     protected $useTimestamps = false;
     protected $dateFormat = 'datetime';
     protected $createdField = 'created_at';
     protected $updatedField = 'updated_at';
     protected $deletedField = 'deleted_at';

     // Validation
     protected $validationRules = [];
     protected $validationMessages = [];
     protected $skipValidation = false;
     protected $cleanValidationRules = true;

     // Callbacks
     protected $allowCallbacks = true;
     protected $beforeInsert = [];
     protected $afterInsert = [];
     protected $beforeUpdate = [];
     protected $afterUpdate = [];
     protected $beforeFind = [];
     protected $afterFind = [];
     protected $beforeDelete = [];
     protected $afterDelete = [];
}

4. Conclusion

If you don’t want to make any changes on the child table when the delete/update action is performed on the parent table then remove CASCADE while defining the foreign key.

If you found this tutorial helpful then don't forget to share.