Laravel migration self referencing foreign key issue

Question

Hi I have a problem to create a table using migration schema builder. The problem occure with table with self referencing foreign key. Here is the code which produce error:

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

Here is the error:

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

goryadd constraint cb_category_parent_id_foreign foreign key (parent_id) referencescb_category(id`) on del ete cascade on update cascade) (Bindings: array ( ))

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

Any idea?

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

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 3

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