Laravel 5 migration - foreign key constraint fails

Question

I'm having problems with running my migration. I have a mysql database with some tables. The specific table is product_blender. Some fields in the table are like this:

id (PK)

area_id (FK)

inhabitants (varchar)

heating_type_id (FK)

...

Now I would like to create another table called installateur_types. The table needs to contain a PK and a varchar field. I would also like to create a FK in product_blender table to the id of my newly created tabel.

This is what I've done:

Created migration to create a table:

public function up()
{
    Schema::create('installateur_types', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('type');
    });
}

public function down()
{
    Schema::drop('installateur_types');
}

Run the migration, this was successful. Table was created with correct fields.

Then I've created the migration to add a FK field to the product_blender table.

public function up()
{
    Schema::table('product_blenders', function ($table) {
        $table->integer('installateurtype_id')->unsigned();
        $table->foreign('installateurtype_id')->references('id')->on('installateur_types')->onDelete('cascade');
    });
}

public function down()
{
    //
}

When I now run the migration I get the following error:

What am I doing wrong?

Does product_blenders table existed previously?

Shapi
– Shapi

2015-10-21 10:27:50 +00:00
Commented Oct 21, 2015 at 10:27 — Shapi
– Shapi, Commented Oct 21, 2015 at 10:27

Akshendra Pratap · Accepted Answer · 2015-10-21 10:29:08Z

If your products_blender table is not empty, then when you add a new column which is not null (which is the default for eloquent), it will be assuming some default value on its own. This value may not be available in the table this new column is referring to, causing the foreign key constraint to fail.

One of the way to get around this is to give a default value to the new column or just make it nullable.

$table->integer('installateurtype_id')->unsigned()->nullable();
$table->foreign('installateurtype_id')->references('id')->on('installateur_types')->onDelete('cascade');

There is one other solution, which turns off this checks, which can be done using DB::statement('SET FOREIGN_KEY_CHECKS=0;'). Then again turn that one for future with DB::statement('SET FOREIGN_KEY_CHECKS=1;'). In you code you can do something like

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$table->integer('installateurtype_id')->unsigned();
$table->foreign('installateurtype_id')->references('id')->on('installateur_types')->onDelete('cascade');
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

Collectives™ on Stack Overflow

Laravel 5 migration - foreign key constraint fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related