SQLSTATE[HY000]: General error: 1005 Can't create table - Laravel 4

sqlstate[hy000]: general error: 1005 can't create table laravel
general error: 1005 can't create table (errno: 150 "foreign key constraint is incorrectly formed")
errno: 150 foreign key constraint is incorrectly formed laravel 6
sqlstate(hy000): general error: 1215 cannot add foreign key constraint
laravel foreign key
laravel add column migration
enum in laravel migration
laravel datetime

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');
    }
}

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.

SQLSTATE[HY000]: General error: 1005 Can't create table, You must first create the table, then create the foreign keys: Schema::create('gigs'​, function($table) { $table->increments('gig_id');  SQLSTATE[HY000]: General error: 1005 Can't create table `vanila`.`#sql-1d4a_1e` #28793. Closed cookiesze opened this issue Jun 11, 2019 · 4 comments Closed

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.

"General error: 1005 Can't create table" Using Laravel Schema Build , SQLSTATE[HY000]: General error: 1005 Can't create table 'nabsh.#sql-5b0_e' (​errno: 121) (SQL: alter table usermeta add constraint  [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table 'laracart.#sql-25b_ 3' (errno: 150) (SQL: alter table `customers_orders` add constraint custome rs_orders_products_id_foreign foreign key (`products_id`) references `produ cts` (`id`) on delete cascade on update cascade)

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.

SQLSTATE[HY000]: General error: 1005 Can't create table, SQLSTATE[HY000]: General error: 1005 Can't create table. Posted 2 years ago by jashnkrish [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table `pgaiiis`.`#sql-8e8_1ab` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `specimen_types` add constraint `spe cimen_types_section_id_foreign` foreign key (`section_id`) references `sections` (`id`)) [PDOException] SQLSTATE

As said by Andrew by making the reference on the table as this:

$table->integer('user_id')->unsigned();

It should work.

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General , [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table `pgaiiis`.`#sql-8e8_1ab`. Posted 2 years ago  General Failure. 1,915 3 3 gold badges 14 14 silver badges 36 36 bronze badges. answered Jan 26 '16 at 5:35. MySQL "ERROR 1005 (HY000): Can't create table. 0.

for those who are still having this issue, make sure the field you setting as foreign key is a primary key or should be unique since a foreign key can only be a primary or unique field. see sample below

Schema::create('users', function (Blueprint $table) {
  $table->increments('id');
  $table->string('username',25)->unique();
 });

Schema::create('another_table', function (Blueprint $table) {
   $table->increments('id');
   $table->string('name', 25);
   $table->foreign('name')->references('username')->on('users')
 });

PDOException: SQLSTATE[HY000]: General error: 1005 Can't , PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'ewt.​dr_private_files_download_permission_directory_role' (errno:  Without seeing your table structure, from your below query it could be that. both column id and supervisor_id doesn't match datatype and size. Make sure both datatype and size are same for both this column

Having error while using foreign, Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table realtimeapp . #sql-1e4_a8 (errno: 150 "Foreign key constraint  #4 was my problem - one of the tables was MyISAM and the script tried to create an InnoDB table. I ran into this problem when I was trying to deploy an old system that was initially running MySQL 5.0 or similar version, where the default storage engine was MyISAM and the scripts were running OK.

Illuminate\Database\QueryException : SQLSTATE[HY000]: General , Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1005 Can't create table `penduraj`.`#sql-13f0_15` (errno: 150  There is one change in Laravel 5.8 that is not mentioned in the official Upgrade Guide but caused me problems - I couldn't create a foreign key migration, and spent half-hour until found out the reasons. So I want to share, maybe you will encounter the same thing.

Resolve General error: 1005 Can't create table (errno: 150) in -, PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'my-​app.#sql-1_188' (errno: 150) in [..] While this may not be clear at  SQLSTATE[HY000]: General error: 1005 Can't create table Posted 2 years ago by jashnkrish Please sign in or create an account to participate in this conversation.

Comments
  • Ok I did this :) still get the same error. I updated my question with my models.
  • Is bands migrated? Or none was? Bands should migrate first, since gigs references it.
  • That was it. I changed the prefix of the migration files.
  • Yep, you need to check the order of migration files
  • I don't know if this was an old requirement, but as of 5.0 you can include the foreign commands within the create callback.
  • This was the answer , I wonder how this magic note not popular .
  • Work For me.Thanks Bro