Laravel migration error Cannot add foreign key contraint

If you’re getting this error message when executing migration on an existing table, you’re probably having the same issue as I was.

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `pages` add constraint pages_category_id_foreign foreign key (`category_id`) references `categories` (`id`))
[PDOException]                                                          
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

The problem is because my ‘categories’ table was created as MyISAM type, which doesn’t support foreign key.

The solution is surprisingly very simple: Convert the table to InnoDB type.

So before I create the new table ‘pages’ which reference to the existing table ‘categories’ with a foreign key, I inserted a line to convert the table to InnoDB type.

We can do that easily with Laravel’s DB::statement.

Like this:

// Default type MyISAM doesn't support foreign key
// Convert table to InnoDB
if (Schema::hasTable('categories')) {
    DB::statement('ALTER TABLE categories ENGINE = InnoDB');
}

if (! Schema::hasTable('pages')) {
    Schema::create('pages', function(Blueprint $table)
    {
        $table->increments('id');
        $table->timestamps();
        $table->string('title', 255);
        ... omitted ...
        $table->integer('category_id')->nullable()->unsigned();
        $table->foreign('category_id')->references('id')->on('categories');
    });
}

I was stuck at this for almost half a day trying to figure out why the migration keeps giving me error (although the tables were created nevertheless).

Hope this helps.


Tags: , , , ,

2 Comments

  • diego

    March 8, 2016

    My DB is on InnoDB, but i have the same problem

    Function UP

    public function up()
    {
    Schema::create(‘videos’, function (Blueprint $table) {
    $table->engine = ‘InnoDB’;

    $table->increments(‘id’);
    $table->string(‘title’, 200);
    $table->enum(‘status’, [‘almacenado’, ‘publicado’, ‘revisado’]);
    $table->string(‘url’, 255);

    $table->integer(‘user_id’)->unsigned(); // Vinculado a una categoria
    $table->foreign(‘user_id’)->references(‘id’)->on(‘users’); // Vinculado a una

    $table->integer(‘category_id’)->unsigned(); // creamos la categoria a vincular
    $table->foreign(‘category_id’)->references(‘id’)->on(‘categories’)->nullable(); // Vinculado a una categoria

    $table->string(‘id_video’);
    $table->longText(‘descripcion’);
    $table->boolean(‘publicado’)->default(false);
    $table->boolean(‘revisado’)->default(false);
    $table->string(‘imagen1’);
    $table->string(‘imagen2’);
    $table->string(‘imagen3’);
    $table->string(‘imagen_principal’);

    $table->timestamps();
    });

    }

    and Category function up

    public function up()
    {
    Schema::create(‘categories’, function (Blueprint $table) {
    $table->engine = ‘InnoDB’;
    $table->increments(‘id’);
    $table->string(‘name’);
    $table->timestamps();
    });
    }

    PSDTA: LAravel 5.2

    Reply

Leave a Reply