Laravel Migrations self-referencing foreign key General error: 1005 Can't create table

Question

Why do I have a problem creating a table using Laravel Migrations Schema Builder?

The problem occurs with a table with a self-referencing foreign key.

Schema::create('cb_category', function($table)
{
    $table->integer('id')->primary()->unique()->unsigned();
    $table->integer('domain_id')->unsigned();
    $table->foreign('domain_id')->references('id')->on('cb_domain'); 
    $table->integer('parent_id')->nullable(); 
    $table->foreign('parent_id')->references('id')->on('cb_category')->onUpdate('cascade')->onDelete('cascade'); 
    $table->string('name');
    $table->integer('level');
});

SQLSTATE[HY000]: General error: 1005 Can't create table 'eklik2.#sql-7d4_e' (errno: 150) (SQL: alter table `cb_category` add constraint cb_category_parent_id_foreign foreign key (`parent_id`) references `cb_category` (`id`) on delete cascade on update cascade) (Bindings: array ())

[PDOException]

SQLSTATE[HY000]: General error: 1005 Can't create table 'eklik2.#sql-7d4_e' (errno: 150)

Answer 1

You have to break this into two Schema blocks, one creating the columns, the other adding the FKs. mysql can't do both at the same time.

Answer 2

Two querys work :

Schema::create('cb_category', function($table)
{
    $table->integer('id')->primary()->unique()->unsigned();
    $table->integer('parent_id')->nullable();  
});

Schema::table('cb_category', function (Blueprint $table) 
{
    $table->foreign('parent_id')->references('id')->on('cb_category')->onUpdate('cascade')->onDelete('cascade');
});

Answer 3

I may be too late for the party, but the official docs claim that the foreign key, in case of integer, must be ->unsigned();

http://laravel.com/docs/4.2/schema#foreign-keys

Note: When creating a foreign key that references an incrementing integer, remember to always make the foreign key column unsigned.

Also, Artisan does not fail if you (as I have) misspell unsigned() and I have spent quite a few hours trying to figure out why the key was not created.

So two things: 1. Always make the foreign key column unsigned in case of incrementing integers 2. Check the spelling of unsigned()

Answer 4

Also a late response but probably a more idiomatic way for Laravel 8:

use App\Models\CbCategory;

...

Schema::create("cb_category", function(Blueprint $table)
{
    $table->id();
    $table->foreignIdFor(CbCategory::class, "parent_id")
        ->constrained()
        ->cascadeOnUpdate()
        ->cascadeOnDelete()
        ->nullable();
});

Please Note: I guessed the class name of CbCategory here. Using the class reference firsthand (instead of the former table name string) enables your static code checkers to pick up on future class name changes.
Also the _id-suffix at the parent_id column name is important.

---

May the following resources quench your thirst for knowledge:

• id(): https://laravel.com/docs/8.x/migrations#column-method-id
• foreignIdFor(): https://laravel.com/api/8.x/Illuminate/Database/Schema/Blueprint.html
• cascadeOnDelete() & cascadeOnUpdate(): https://laravel.com/api/8.x/Illuminate/Database/Schema/ForeignKeyDefinition.html

Answer 5

    Schema::create('categories', function (Blueprint $table) {
        $table->id();
        $table->integer('parent_id')->unsigned();
        $table->foreign('parent_id')->on('categories')->references('id');
    });

i got same error when i used this code, after change "$table->integer('parent_id')->unsigned()" to "$table->bigInteger('parent_id');" my problem solved.

The point here is to make sure that the type of foreign key is the same as the primary key.

Answer 6

Schema::create('cb_category', function (Blueprint $table) {
        $table->increments('id')->unsigned();
        $table->integer('domain_id')->unsigned();
        $table->foreign('domain_id')->references('id')->on('cb_domain');
        $table->integer('parent_id')->nullable();
        $table->foreign('parent_id')->references('id')->on('cb_category')->onUpdate('cascade')->onDelete('cascade');
        $table->string('name');
        $table->integer('level');
    });

Try this

Answer 7

I think you have another table that references the current table that you want to create. I had this problem and remove that table and my problem was solved

Answer 8

Since Laravel 8+ you don't have to break into two Schema blocks. You can use foreignIdFor(CbCategory::class, 'cb_category_id') and it will create a column named cb_category_id

Ex.

Schema::create("cb_category", function(Blueprint $table)
{
    $table->id();
    $table->foreignIdFor(CbCategory::class, 'cb_category_id')->nullable()->constrained()->cascadeOnUpdate()->cascadeOnDelete();
});

Any additional column modifiers (Ex. nulleable) must be called before the constrained method.

You can use a second parameter in foreignIdFor for the referencing column name (in case it isn't 'id') NOT for the name you want it to have, in your case the name will automatically be 'cb_category_id'

Answer 9

BIG

Schema::create('categories', function (Blueprint $table){
$table->id();
$table->bigInteger('parent_id')->unsigned()->nullable();
$table->foreign('parent_id')->references('id')->on('categories')->onDelete('cascade')->onUpdate('restrict');