phplaravelforeign-keysconstraintsdatabase-migration

Stuck at (erno: 150 "Foreign key constraint is incorrectly formed,")


I'm new to learning Laravel 11, and I'm stuck with the (erno: 150 "Foreign key constraint is incorrectly formed,") and despite trying various suggestions I’ve seen, I still can't resolve it. Could you please help me identify where I went wrong and how to fix it?

This is the expenses table

 public function up(): void
    {
        Schema::create('expenses', function (Blueprint $table) {

            $table->id();
            $table->increments('category_id');
            $table->foreign('category_id')->references('id')
            ->on(table:'categories')->onDelete('cascade')->onUpdate(action:'cascade');
            $table->decimal('amount',8, 2); 
            $table->date('entry_date'); 
            $table->timestamps();
            $table->engine = 'InnoDB';

        });
    }

This is the categories table

   public function up(): void
    {
        Schema::create('categories', function (Blueprint $table) {

            $table->increments('id');
            $table->string('category_name'); 
            $table->string('description')->nullable(); 
            $table->timestamps();
        }); 
    }

I need the foreign key category_id in the expenses table to be connected to the categories table. When I update a category, I want the category_id in the expenses table to also update, and the same goes for deletion. If a specific category_name is deleted, I want the corresponding records in the expenses table to be removed as well.


Solution

  • The category_id column, you declared in the expenses table can not be incremented. You have to define the following,

    public function up(): void
    {
        Schema::create('expenses', function (Blueprint $table) {
        $table->id(); // Primary key for the expenses table
        $table->unsignedBigInteger('category_id'); // Set up the foreign key constraint
        $table->decimal('amount', 8, 2); 
        $table->date('entry_date'); 
        $table->timestamps();
        $table->engine = 'InnoDB';
    
        $table->foreign('category_id') // Foreign key reference to the categories table
            ->references('id')
            ->on('categories')
            ->onDelete('cascade')
            ->onUpdate('cascade');
        });
    }