25

I've just created a new Laravel 4 project and am finding strange things happening with the foreign key aspect of the schema builder. If I use the ->foreign() method in any of my migrations I get thrown MySQL errors 150 and general error 1005. According to the documentation at laravel.com/docs the two scenario's at the bottom should work? Anyone know why they don't?

The following does work:

    Schema::create('areas', function($table)
    {
        $table->engine ='InnoDB';
        $table->increments('id');

        $table->integer('region_id')->references('id')->on('regions');

        $table->string('name', 160);
        $table->timestamps();
    });

But these two do not work:

    Schema::create('areas', function($table)
    {
        $table->engine ='InnoDB';
        $table->increments('id');

        $table->foreign('region_id')->references('id')->on('regions');

        $table->string('name', 160);
        $table->timestamps();
    });

    Schema::create('areas', function($table)
    {
        $table->engine ='InnoDB';
        $table->increments('id');

        $table->integer('region_id');
        $table->foreign('region_id')->references('id')->on('regions');

        $table->string('name', 160);
        $table->timestamps();
    });
Improve this question
3
  • possible duplicate of Laravel 4 Migrations throwing 1072 error Commented Jun 5, 2013 at 8:31
  • 4
    This questions is about why the above doesn't work, the other is about MySQL 1072 errors. Commented Jun 5, 2013 at 22:26
  • Just getting into Laravel and hit this too. My workaround was to add foreign keys using a separate migration. It works but feels kind of wrong. Commented Oct 20, 2013 at 20:04

5 Answers 5

Reset to default
53

Check your id type. Laravel 4 creates an incremental id with a int(10) unsigned. If you create a basic integer and try to put a foreign key on it, it will fail.

As suggested in the documentation at this link, you should create the foreign id with $table->unsignedInteger(YOUR_ID_NAME); to make it work.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

For example: (users table) $table->increments('id'); and (posts table) $table->unsignedInteger('user_id'); \n $table->foreign('user_id')->references('id')->on('users');
I was using the correct type like so $table->integer('user_id')->unsigned(); And the unsigned property was being set up in the DB but it didn't work for me. I updated my code to use unsignedInteger and everything now works surprisingly
Had the same issue myself - Dayle Rees has explained it well (with examples) daylerees.com/codebright/eloquent-relationships
@alairock great example because a noob error I made was trying to create the foreign key before the column exists. First declare the column, then make it a foreign key. +1 for that!
7

Also some answers over at this question "General error: 1005 Can't create table" Using Laravel Schema Build and Foreign Keys

A Summary of the answers listed there, including mine:

  1. Foreign Keys generally require InnoDb, so set your default engine, or explicitly specify $table->engine = 'InnoDB'; If your table is already created and has defaulted to MyISAM, you may need to alter it.

  2. Foreign keys require the referenced table to exist. Make sure the referenced table is created in an earlier migration, prior to creating the key. Consider creating the keys in a separate migration to be sure.

  3. Foreign Keys require the data type to be congruent. Check whether the referenced field is the same type, whether its signed or unsigned, whether it's length is the same (or less).

  4. If you are switching between hand coding migrations, and using generators, make sure you check the id type you are using. Artisan uses increments() by default but Jeffrey Way appears to prefer integer('id', true).

Improve this answer

1 Comment

My issue was that it was a table that was previously created and had defaulted to MyISAM. After updating the table being referenced to InnoDB everything worked great.
5

Had same problem day ago.

Root of problem is : column with foreign key must be same type as that key. And you have different types: INT/UNSIGNED INT

this makes id an UNSIGNED INT

$table->increments('id');

and this makes region_id an INT

$table->integer('region_id')->references('id')->on('regions');

To solve this, make region_id an UNSIGNED INT too

$table->integer('region_id')->unsigned()->references('id')->on('regions'); 
                              ^^^^^^^^^ note here

Documentation of Laravel has mention about this :

Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.

Improve this answer

Comments

2

It works, but sometimes you just have to be careful and try to understand what is happening behind the scene.

As I said in my comment. When you first ran the migration without creating the related column, Laravel migration services created your table and then, when you tried to migrate again it will always give you an error saying that the table already exists.

So you just have to drop table areas and run php artisan migrate again to fix it all.

EDIT:

I just created your migration (below) here and it worked.

As you can see I'm not using MySQL, so it must be a MySQL problem. Check MySQL foreign key documentation to see if your metadata fits in InnoDB requirements: http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html.

<?php

use Illuminate\Database\Migrations\Migration;

class CreateAreasTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('regions', function($table)
         {
             // $table->engine = 'InnoDB';
             $table->increments('id');
             $table->string('name', 160)->unique();
             $table->timestamps();
        });

        Schema::create('areas', function($table)
        {
            // $table->engine ='InnoDB';
            $table->increments('id');

            $table->integer('region_id');
            $table->foreign('region_id')->references('id')->on('regions');

            $table->string('name', 160);
            $table->timestamps();
        });     
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
    Schema::drop('areas');
    Schema::drop('regions');
    }

}

enter image description here

Improve this answer

1 Comment

That didn't work. I dropped the database, created a new one, ran php artisan migration:install followed by php artisan migrate and the errors, still occur, but only if I include any mention of ->foreign().
1

antonio carlos is right, make sure that you created first the reference table of your foreign key.

try migrate first the tables without foreign keys , then make another migration assigning the foreign keys. on this state, laravel is sure that the reference key(s) are existing. and you dont have to drop tables during the artisan errors.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.