Cannot add foreign key constrain on delete cascade

Question

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?

Shouldn't he foreign logic be set in the product_product_attribute table? Same as you did you the product_id/pp_id? (does it work without the onDelete(cascade)?) — Mauro Baptista, May 15 '20 at 11:01
Assume you have `products`, `product_attributes`, `product_product_attribute` tables. Do you want, if a `product` is deleted then associated `product_attributes` and `product_product_attribute` will be cascaded? Would you please give the schema of `products` table? — bdtiger, May 15 '20 at 11:56

score 1 · Accepted Answer · answered May 15 '20 at 12:16

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

Cannot add foreign key constrain on delete cascade

1 Answers1