`The foreign key constraint is incorrectly formed` in Phinx Migrations (CakePhp3)

Question

I have these two migration files, and I want to add the primary key of "clients" as a foreign key in "samples" but when migrating, it gives me an error that "The foreign key constraint is incorrectly formed".

Here is the screenshot of the command line:

Here is my code:

CreateClients

<?php
use Migrations\AbstractMigration;

class CreateClients extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('clients');

        $table->addColumn('name', 'string', [
            'limit' => 100,
            'null' => false,
        ]);
        $table->addColumn('title', 'string', [
            'default' => null,
            'limit' => 100,
            'null' => true,
        ]);
        $table->addColumn('street', 'string', [
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('city', 'string', [
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('state', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('zipcode', 'biginteger', [
            'limit' => 10,
            'null' => false,
        ]);
        $table->addColumn('country', 'string', [
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('phone', 'biginteger', [
            'limit' => 10,
            'null' => false,
        ]);
        $table->addColumn('emailprimary', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('emailsecondary', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('username', 'string', [
            'limit' => 255,
            'null' => false,

        ])->addIndex(array('username'), array('unique' => true));
        $table->addColumn('password', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('billing_title', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('billing_street', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('billing_city', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('billing_state', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('billing_zipcode', 'biginteger', [
            'default' => null,
            'limit' => 20,
            'null' => true,
        ]);
        $table->addColumn('billing_country', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->addColumn('billing_phone', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => true,
        ]);
        $table->create();
    }
}

CreateSamples

<?php
use Migrations\AbstractMigration;

class CreateSamples extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $table = $this->table('samples');

        $table->addColumn('name', 'string', [
            'limit' => '100',
            'null' => false,
        ]);
        $table->addColumn('client_id', 'integer', [
            'null' => false,
        ]);
        $table->addForeignKey('client_id', 'clients', 'id', array('delete'=> 'SET_NULL', 'update'=> 'NO_ACTION'));
        $table->save();

        $table->create();
    }
}

Answer 1

In your CreateSamples migration, you define the client_id column as not null. However, when you define the foreign key, you do it as on delete, set null. That cannot go together. Instead of SET_NULL, CASCADE or RESTRICT would be a better choice, depending on the logic of your application. (Restrict would prevent you from deleting clients with samples.) You could also define the client_id column as 'null' => 'true'. So, for example, try this:

$table->addForeignKey('client_id', 'clients', 'id', array('delete'=> 'CASCADE', 'update'=> 'NO_ACTION'));

That will delete the samples when you delete the client.

More explanation about that MySQL's behavior here.