Browse Tag

migrations

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.