General error: 1005 Can't create table ,Foreign key constraint is incorrectly formed in laravel

3
votes

why laravel schema reply

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table test.#sql-13cc_d0 (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table cities add constrai nt cities_provinces_id_foreign foreign key (provinces_id) references provinces (id) on delete cascade)

[PDOException] SQLSTATE[HY000]: General error: 1005 Can't create table test.#sql-13cc_d0 (errno: 150 "Foreign key constraint is incorrectly formed")

first table

Schema::create('provinces', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->boolean('is_enable');
        $table->boolean('is_deletable');
        $table->boolean('is_editable');
        $table->boolean('deleted');
        $table->timestamps();
    });

second table

    Schema::create('cities', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->integer('province_id')->unsigned()->index();
        $table->boolean('is_enable');
        $table->boolean('is_deletable');
        $table->boolean('is_editable');
        $table->boolean('deleted');
        $table->timestamps();


        $table->foreign('province_id')
            ->references('id')->on('provinces')
            ->onDelete('cascade');

    });
4
laravellaravel-5.2

4 Answers

3
votes

You should make sure, your provinces table migration is running before cities table migration.

You should change provinces or cities migration file name to make sure timestamp at the beginning of provinces table migration file will be before cities table.

4
votes

The time go ahead but i will comment something else.

Andile is right! You must have the same type. In this case, the foreign key should have the type bigInteger because you have bigIncrements on the reference table.

I think thats the solution for a little and boring debug. (sorry for my english)

1
votes

Try to create table first and then add constraint:

Schema::create('cities', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->integer('province_id')->unsigned()->index();
    $table->boolean('is_enable');
    $table->boolean('is_deletable');
    $table->boolean('is_editable');
    $table->boolean('deleted');
    $table->timestamps();
});

Schema::table('cities', function (Blueprint $table) {
    $table->foreign('province_id')->references('id')->on('provinces')->onDelete('cascade');
});
0
votes

The foreign key and referencing table must be same type, the auto generated id are of bigInteger, so instead of defining your column as Integer use bigInteger insteated. That Solved the problem for me