CODEWITHSUNDEEP
laravellaravel-7laravel-migrations
SPQRInc
SPQRInc

Reputation: 188

Cannot add foreign key constrain on delete cascade

I am trying to create a cascade on two migrations:


Schema::create('product_product_attribute',
    function ($table) {
        $table->bigIncrements('id');
        $table->bigInteger('product_id')->unsigned();
        $table->bigInteger('product_attribute_id')->unsigned();
        $table->boolean('custom')->nullable();
        $table->string('title')->nullable();
        $table->string('unit')->nullable();
        $table->string('type')->nullable();
        $table->text('value')->nullable();
        $table->float('price')->nullable();
        $table->bigInteger('position')->nullable();
        $table->foreign('product_id', 'pp_id')->references('id')
            ->on('products')->onDelete('cascade');
        $table->timestamps();
    });


Schema::create('product_attributes', function ($table) {
    $table->bigIncrements('id');
    $table->string('title');
    $table->string('unit')->nullable();
    $table->string('type');
    $table->float('price')->nullable();
    $table->nestedSet();
    $table->foreign('id')->references('product_attribute_id')
        ->on('products')->onDelete('cascade');
    $table->timestamps();
});

So what it is supposed to do:

If the Product that contains an attribute, both, the attribute and the pivot table for the attribute should be cascading.

This fails with:

Illuminate/Database/QueryException with message 'SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table product_attributes add constraint product_attributes_id_foreign foreign key (id) references products (product_attribute_id) on delete cascade)'

Where did I do my mistake?

Upvotes: 1

Views: 1845

Answers (1)

bdtiger
bdtiger

Reputation: 521

You are trying to add the foreign key reference on the primary key of product_attributes table and wrong reference column. The correct reference to the products table 

$table->unsignedBigInteger('product_id');
$table->foreign('product_id')->references('id')
            ->on('products')->onDelete('cascade');

The full schema of product_attributes table is

Schema::create('product_attributes', function ($table) {
        $table->bigIncrements('id');
        $table->string('title');
        $table->string('unit')->nullable();
        $table->string('type');
        $table->float('price')->nullable();
        $table->nestedSet();
        $table->unsignedBigInteger('product_id');
        $table->foreign('product_id')->references('id')
            ->on('products')->onDelete('cascade');
        $table->timestamps();
    });

Upvotes: 1

Related Questions