Where is my mistake? Cannot delete or update a parent row: a foreign key constraint fails

Question

I'm trying to delete brands and vendors from my database, they are related to each other and brand is related to a product, I'm deleting these relationships before doing the final delete (at least I think I am) and I'm getting this error and I'm not sure what I'm missing. Originally in the brand model there was no relationship to the product, the relationship was in the product model. I've added the relationship to the brand model with no luck, still the same result.

Table structure

Schema::create('vendors', function (Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('image')->nullable();
            $table->timestamps();
        });

Schema::create('brands', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->nullable();
            $table->integer('vendor_id')->unsigned();;
            $table->foreign('vendor_id')->references('id')->on('vendors');
            $table->timestamps();
        });

Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('code');
            $table->string('sku')->nullable();
            $table->text('description_spanish');
            $table->text('description_english');
            $table->string('image')->nullable();
            $table->string('discount');
            $table->string('cif')->nullable();
            $table->string('color')->nullable();
            $table->string('color_ab')->nullable();
            $table->integer('brand_id')->unsigned();
            $table->timestamps();

            $table->foreign('brand_id')->references('id')->on('brands');
        });

Models and Relationships

class Vendor extends Model
{
    protected $hidden = ['created_at','updated_at'];

    public function  brands(){
        return $this->hasMany(Brand::class);
    }
}

class Brand extends Model
{
    public function vendor() {
        return $this->belongsTo(Vendor::class);
    }

    public function products() {
        return $this->hasMany(Product::class);
    }

}

class Product extends Products
{
    public function brand()
        {
            return $this->belongsTo(Brand::class);
        }
}

Destroy Functions in the Controllers

Vendor destroy function

public function destroy($id)
    {
        DB::beginTransaction();
        $vendor = Vendor::findOrFail($id);
        $vendor->brands()->delete();
        $vendor->delete();
        DB::commit();

    }

Brand destroy function

public function destroy($id)
    {
        DB::beginTransaction();
        $brand= Brand::findOrFail($id);
        $brand->vendor()->delete();
        $brand->products()->delete();
        $brand->delete();
        DB::commit();
    }

Product destroy funcion

public function destroy($id)
    {
        $product = Product::findOrFail($id);
        DB::beginTransaction();
        $product->sizes()->detach();
        $product->tags()->detach();
        $product->fields()->detach();
        $product->countries()->detach();
        $this->removeProductImage($product);
        $product->exportationFactors()->delete();
        $product->delete();
        DB::commit();
    }

when I try to delete a Vendor I recieve this error

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update
 a parent row: a foreign key constraint fails (`sondel`.`products`, CONSTRAINT 
`products_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` 
(`id`)) (SQL: delete from `brands` where `brands`.`vendor_id` = 1 and 
`brands`.`vendor_id` is not null)

and when I try to delete a Brand I have basically the same error

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update 
a parent row: a foreign key constraint fails (`sondel`.`brands`, CONSTRAINT 
`brands_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` 
(`id`)) (SQL: delete from `vendors` where `vendors`.`id` = 1)

Answer 1

If you want to always delete all child relationships when deleting a record, you can do it in the boot function of the model on the deleting method. Something like this:

Vendor Model

class Vendor extends Model
{
    public static function boot() {
        parent::boot();
        // when you are deleting a Vendor, also delete all related brands
        static::deleting(function($vendor){ 
            $vendor->brands->each(function($brand) {
                $brand->delete();
            });
        });
    }

    protected $hidden = ['created_at','updated_at'];

    public function  brands(){
        return $this->hasMany(Brand::class);
    }
}

Brand Model

class Brand extends Model
{
    public static function boot() {
        parent::boot();
        // when you are deleting a Brand, also delete all related products
        static::deleting(function($brand){ 
            $brand->products->each(function($product) {
                $product->delete();
            });
        });
    }

    public function vendor() {
        return $this->belongsTo(Vendor::class);
    }

    public function products() {
        return $this->hasMany(Product::class);
    }
}

Product Model

class Product extends Products
{
    public static function boot() {
        parent::boot();
        // when you are deleting a Product, also delete/detach all you need
        static::deleting(function($product){ 
            /*
            $product->sizes()->detach();
            $product->tags()->detach();
            $product->fields()->detach();
            $product->countries()->detach();
            $product->exportationFactors->each(function($exportationFactor) {
                $exportationFactor->delete();
            });
            */
        });
    }

    public function brand()
        {
            return $this->belongsTo(Brand::class);
        }
}

Then in your controllers just delete the record corresponding to each controller.

Vendor destroy function

public function destroy($id)
{
    DB::beginTransaction();
    Vendor::findOrFail($id)->delete();
    DB::commit();

}

Brand destroy function

public function destroy($id)
{
    DB::beginTransaction();
    Brand::findOrFail($id)->delete();
    DB::commit();
}

Product destroy funcion

public function destroy($id)
{
    $product = Product::findOrFail($id);
    DB::beginTransaction();
    $this->removeProductImage($product);
    $product->delete();
    DB::commit();
}

Answer 2

The problems you are having is in the products table, There is 2 ways you can solve this problem:

Solution 1:

Just like Yovi's answer states, you could simply add onDelete('cascade') to your foreign key on your brands & products table.

brands table:

$table->foreign('vendor_id')->references('id')->on('vendors')->onDelete('cascade');

products table:

$table->foreign('brand_id')->references('id')->on('brands')->onDelete('cascade');

Then your controllers destroy method should look like this: vendor destroy function:

public function destroy($id)
{
    $vendor = Vendor::findOrFail($id);
    $vendor->delete();

}

brands destroy method:

public function destroy($id)
{
    $brand= Brand::findOrFail($id);
    $brand->delete();
}

Solution 2:

If you want to delete your rows manually, you just have the wrong order set on your destroy methods. You must first delete the youngest child starting at products -> brands -> vendors. Your methods should look like this:

Vendor destroy function:

public function destroy($id)
{
    DB::beginTransaction();
    $vendor = Vendor::findOrFail($id);

    foreach($vendor->brands() as $brand){
        $brand->products()->delete();
    }

    $vendor->brands()->delete();
    $vendor->delete();
    DB::commit();
}

Brand destroy function:

public function destroy($id)
{
    DB::beginTransaction();
    $brand= Brand::findOrFail($id);
    $brand->products()->delete();
    $brand->delete();
    DB::commit();
}

Overall I find solution 1 to be a lot cleaner.

Answer 3

Add onDelete('cascade') on every $table->foreign('<Column>') you have.

Example:

$table->foreign('vendor_id')->references('id')->on('vendors')->onDelete('cascade');

Then no need to delete all the children first, just delete the parent.