SQLSTATE[HY000]: General error: 1005 Can't create table - Laravel 4
Solution 1
You must first create the table, then create the foreign keys:
Schema::create('gigs', function($table)
{
$table->increments('gig_id');
$table->dateTime('gig_startdate');
$table->integer('band_id')->unsigned();
$table->integer('stage_id')->unsigned();
});
Schema::table('gigs', function($table)
{
$table->foreign('band_id')
->references('band_id')->on('bands')
->onDelete('cascade');
$table->foreign('stage_id')
->references('stage_id')->on('stages')
->onDelete('cascade');
});
And your bands
table should migrate first, since the gigs
is referencing it.
Solution 2
While this doesn't apply to OP, others might have this issue:
From the bottom of the Laravel Schema docs:
Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.
You can do this via $table->integer('user_id')->unsigned();
when creating your table in the migration file.
Took me a few minutes to realize what was happening.
Solution 3
For those whom other answers doesn't help, the same error throws also when you try to use 'SET_NULL'
action on non-nullable column.
Solution 4
Nothing of the above worked for me ! But this did : I just changed integer to unsignedInteger
$table->unsignedBigInteger('user_id')->unsigned();
Solution 5
As said by Andrew by making the reference on the table as this:
$table->integer('user_id')->unsigned();
It should work.
Gilko
Updated on March 11, 2021Comments
-
Gilko about 3 years
I get this error when I do php artisan migrate. Is there something wrong in my migration files? Or is it possible my models are wrong coded? But the migrations should work even there is something wrong in the models?
[Exception] SQLSTATE[HY000]: General error: 1005 Can't create table 'festival_aid.#sql- 16643_2033' (errno: 150) (SQL: alter table `gigs` add constraint gigs_band_ id_foreign foreign key (`band_id`) references `bands` (`band_id`) on delete cascade) (Bindings: array ( )) [PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table 'festival_aid.#sql- 16643_2033' (errno: 150)
gigs migration
public function up() { Schema::create('gigs', function($table) { $table->increments('gig_id'); $table->dateTime('gig_startdate'); $table->integer('band_id')->unsigned(); $table->integer('stage_id')->unsigned(); $table->foreign('band_id') ->references('band_id')->on('bands') ->onDelete('cascade'); $table->foreign('stage_id') ->references('stage_id')->on('stages') ->onDelete('cascade'); }); public function down() { Schema::table('gigs', function($table) { Schema::drop('gigs'); $table->dropForeign('gigs_band_id_foreign'); $table->dropForeign('gigs_stage_id_foreign'); }); }
bands migration
public function up() { Schema::create('bands', function($table) { $table->increments('band_id'); $table->string('band_name'); $table->text('band_members'); $table->string('band_genre'); $table->dateTime('band_startdate'); }); } public function down() { Schema::table('bands', function(Blueprint $table) { Schema::drop('bands'); }); }
Model Band
<?php class Band extends Eloquent { protected $primaryKey = 'band_id'; public function gig() { return $this->hasOne('Gig', 'band_id', 'band_id'); } }
Model Gig
<?php class Gig extends Eloquent { protected $primaryKey = 'gig_id'; public function gig() { return $this->belongsTo('Band', 'band_id', 'band_id'); } public function stage() { return $this->belongsTo('Stage', 'stage_id', 'stage_id'); } }