Cannot add foreign key constraint using phinx

Question

I'm trying to use phinx to add a couple of related tables. I created my migration:

<?php

use Phinx\Migration\AbstractMigration;

class CreatesCognitiveMappingTables extends AbstractMigration
{
    public function up()
    {
        $this->table("cognitive_mapping")
            ->changeColumn('id', 'biginteger', ['identity' => true])
            ->addColumn("user_id", "biginteger", ["signed" => false])
            ->addColumn("participant_id", "integer")
            ->addColumn("session_number", "integer")
            ->addColumn("date_time", "datetime")
            ->addColumn("image_file_link", "text")
            ->addColumn("time", "decimal", ["precision" => 10, "scale" => 5])
            ->addColumn("test_trial_complete", "boolean")
            ->addTimestamps()
            ->create();

        $child = $this->table("cognitive_mapping_dragdrop_results");
        $child
            ->changeColumn('id', 'biginteger', ['identity' => true])
            ->addColumn("cognitive_mapping_id", "biginteger", ["signed" => false])
            ->addColumn("box_number", "integer")
            ->addColumn("correct_answer", "text")
            ->addColumn("given_answer", "text")
            ->addColumn("accuracy", "boolean")
            ->addTimestamps()
            ->create();

        $child
            ->addForeignKey("cognitive_mapping_id", "cognitive_mapping", "id", ["delete" => "CASCADE"])
            ->save();
    }

    public function down()
    {
        $this->table("cognitive_mapping_dragdrop_results")->drop()->save();
        $this->table("cognitive_mapping")->drop()->save();
    }
}

But when I run the migration I get an error message saying it can't add the foreign key constraint:

I've tried various permutations of this including creating the child table in a different migration file, removing the cascade, explicitly unsigning the primary key in the parent table. All of these give me the same error. There's something I'm not seeing.

I'm using php 7.3 and phinx version 0.12.1

Any ideas?

score 1 · Answer 1 · answered Jun 12 '20 at 01:24

Ah, just figured it out.

Even though the foreign key constraint doesn't get added, the tables are still created so I was able to review the table structure:

I noticed that the primary key in the parent table didn't get changed to an unsigned big int even though it's migration chain didn't fail.

I suspected that because of this when I tried to add the constraint on the child table the fields didn't match (couldn't hold the same data) so it failed.

I also suspected that the field didn't get changed because I was trying to change the column before it was actually created, so I refactored my migration to break up the steps:

class CreatesCognitiveMappingTables extends AbstractMigration
{
    public function up()
    {
        // * set up the bulk of the parent table
        $parent = $this->table("cognitive_mapping")
            ->addColumn("user_id", "biginteger", ["signed" => false])
            ->addColumn("participant_id", "integer")
            ->addColumn("session_number", "integer")
            ->addColumn("date_time", "datetime")
            ->addColumn("image_file_link", "text")
            ->addColumn("time", "decimal", ["precision" => 10, "scale" => 5])
            ->addColumn("test_trial_complete", "boolean")
            ->addTimestamps();

        // * create the table
        $parent->create();

        // * now that the table is created, update it to convert the primary key to a unsigned big integer
        $parent
            ->changeColumn('id', 'biginteger', ['identity' => true, 'signed' => false])
            ->save();

        // * Make the child table
        $child = $this->table("cognitive_mapping_dragdrop_results");
        $child
            ->changeColumn('id', 'biginteger', ['identity' => true])
            ->addColumn("cognitive_mapping_id", "biginteger", ["signed" => false]) // ! note that our to-be foreign key field is already an unsigned big int
            ->addColumn("box_number", "integer")
            ->addColumn("correct_answer", "text")
            ->addColumn("given_answer", "text")
            ->addColumn("accuracy", "boolean")
            ->addTimestamps()
            ->create();

        // * Now that both our foreign key and primary key fields match we can add the constraint
        $child
            ->changeColumn('id', 'biginteger', ['identity' => true, "signed" => false])
            ->addForeignKey("cognitive_mapping_id", "cognitive_mapping", "id", ["delete" => "CASCADE"])
            ->save();
    }

    public function down()
    {
        $this->table("cognitive_mapping_dragdrop_results")->drop()->save();
        $this->table("cognitive_mapping")->drop()->save();
    }
}

I was able to run the migration successfully:

And the constraint is added correctly:

so nice :)

Cannot add foreign key constraint using phinx

1 Answers1