How to use foreign keys migration with database migrations. (errno: 150 “Foreign key constraint is incorrectly formed”)?

CodeIgniter 4 has a handy solution for migrations and seeders. Without the usage of foreign keys, everything is working perfectly. But when I use foreign keys I get “Unable to add foreign key”.

This is because of the order of happenings:

Quick example:

table bar
 -------------------
| id | name | fooid |
FOREIGN KEY fooid REFERENCES foo.id

table foo
 ----------
| id | name|

So when I now run php spark migrate or php spark migrate:refresh the foreign key can not be set because the bar table is created, but the referenced foo table does not exist yet.

Technically I could run a separate function that I run after my migration, but I like the one command of php spark migrate and everything is done.

What is the correct way to solve this issue?

This is my create table created code:

CREATE TABLE `bar` (
    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NULL DEFAULT NULL,
    `fooid` int(11) UNSIGNED NOT NULL,
    CONSTRAINT `pk_bar` PRIMARY KEY(`id`),
    CONSTRAINT `bar_fooid_foreign` FOREIGN KEY(`fooid`) REFERENCES `foo` (`id`) ON DELETE SET NULL,
    KEY `fooid` (`fooid`)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

CREATE TABLE `foo` (
    `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NULL DEFAULT NULL,
    CONSTRAINT `pk_foo` PRIMARY KEY(`id`),
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

---

Addendum

I think I find the direction of the issue. Noticed from the comments, it is correct, the order of creation of the tables is not correct. This can be fixed by changing the timestamps in the filename, so that foo gets created before bar. But as this is not fixing the issue, I found something else: This is code I’m using to migrate bar:

class Bar extends Migration
{
    public function up()
    {
        $fields = [
            'id' => [
                'type' => 'int',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'name' => [
                'type' => 'varchar',
                'constraint' => 255,
                'default' => null,
            ],
            'fooid' => [
                'type' => 'int',
                'constraint' => 11,
                'unsigned' => true,
            ],
        ];
        $this->forge->addField($fields);

        $this->forge->addPrimaryKey('id');
        $this->forge->addKey('fooid');

        $this->forge->addForeignKey('fooid', 'foo', 'id', '', 'SET NULL');

        $this->forge->createTable('bar');
    }

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

This generates a fooid int(11) UNSIGNED NOT NULL. The issue is, that foodid can not be null but the fk sets the value to null on delete.

But… the default for null of a field is 'null' => true and even if I add this manually, it is not generating the nullable field.

Answer

Simply rename your migration timestamp prefixes such that the table creation of foo comes before that of bar. For example, if the migration file names are as follows:

app/Database/Migrations/2022-02-16-101819_CreateBarMigration.php
app/Database/Migrations/2022-04-22-101819_CreateFooMigration.php ❌

Rename their migration timestamp prefixes in that the referenced table (foo) comes first.

app/Database/Migrations/2022-04-22-101819_CreateBarMigration.php
app/Database/Migrations/2022-02-16-101819_CreateFooMigration.php ✅

Lastly, rerun the pending migrations. php spark migrate.

---

Addendum 1

In reference to your newly edited question description, move the table creation query of foo to come before that of bar. I.e:

CREATE TABLE `foo` ( ...)

CREATE TABLE `bar` ( ...)

---

Addendum 2

2022-02-16-101819_CreateFooMigration.php

<?php

namespace AppDatabaseMigrations;

use CodeIgniterDatabaseMigration;

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

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

2022-04-22-101819_CreateBarMigration.php

<?php

namespace AppDatabaseMigrations;

use CodeIgniterDatabaseMigration;

class CreateBarMigration extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id'          => [
                'type'           => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'name'       => [
                'type'       => 'VARCHAR',
                'constraint' => '100',
            ],
            'foo_id' => [
                'type' => 'INT',
                'constraint'     => 11,
                'unsigned'       => true,
                'null' => true,
            ],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('foo_id', 'foo', 'id', 'CASCADE', 'RESTRICT' );
        $this->forge->createTable('bar');
    }

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