Foreign Constraint is incorrectly formed issue (laravel)

Question

I'm hitting a bug where I can't run some laravel migrations as I'm getting the error (errno: 150 "Foreign key constraint is incorrectly formed")

I have read around the subject and can't seem to work out what my problem is. The foreign key I wish to set is an unsigned integer. The data-types are the same across both tables, etc.

Here is my migration:

  Schema::create('sample_migration', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('subject_id')->unsigned();
        $table->string('1');
        $table->string('2');
        $table->integer('3');
        $table->string('4');
        $table->integer('5');
        $table->integer('type')->unsigned();
        $table->timestamps();

       $table->foreign('type')->references('subject_type')->on('some_table');
    });

On some_table I have subject_type as an unsigned integer. Both use the same collation and encoding.

If I add a unique() flag on the relevant fields then the migration runs smoothly, but I don't want this as there will be duplicate data.

Is there something that I am missing?

Read up on the conditions for creating foreign keys here https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html — P.Salmon, Apr 02 '18 at 09:06
Hi @P.Salmon - I think that I match all of those criteria. This should just be a very simple operation and I have done this with Laravel many times in the past. I am using the same data type, the same length, ensuring integers are unsigned, etc. It runs if the field has the `unique()` flag set, but not otherwise... — roo, Apr 02 '18 at 09:08
Do both tables use the `InnoDB` engine? Can you post the migration of `some_table`? — Jonas Staudenmeir, Apr 02 '18 at 12:44

score 0 · Answer 1 · answered Apr 02 '18 at 12:43

I assume you are trying to add a foreign key to a non-unique column.

There are 2 ways you can solve this depending on your business logic.

Firstly you can opt to create a unique key on multiple columns and reference the same on sample_migration table.

Secondly you can make subject_type column as an index key like this

$table->integer('subject_type')->unsigned()->index();

This method still have foreign key to non unique column which I personally don't recommend as it may have bad consequences as stated here: https://stackoverflow.com/a/2179372/3929188

Foreign Constraint is incorrectly formed issue (laravel)

1 Answers1