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.
diego
March 8, 2016My 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
Andrews Ang
March 8, 2016I suspect you may have the same problem as I had before.
The order of the migration file is important. The categories table must be created BEFORE the videos table.
Take a look at http://blog.kongnir.com/2015/03/08/laravel-order-of-migrations-with-foreign-keys/