Laravel模型delete()函数异常：非预期SQL致约束失败

问题 #

尝试使用Laravel模型的delete()函数删除t_category表中的一条分类记录时，生成的SQL语句不符合预期：

delete from t_category where t_category.catLeft > 505 and t_category.catRight < 518 order by t_category.catLeft asc

这条SQL触发了SQL约束失败错误（错误码1451）。同时尝试用destroy()传入分类ID也出现相同错误，疑惑为何Laravel不通过ID查找并删除目标记录。

控制器remove方法代码 #

/**
 * Remove the category
 * @param  Request $request
 * @param  String  $no      category's no
 * @return \Illuminate\Http\RedirectResponse Redirect to the list of category
 */
public function remove(Request $request, $no){
    // Get the category
    $category = Category::findByNo($no);

    if ($category == null)
        return redirect('/gest/categories');

    if ($category->isRoot())
        return redirect('/gest/categories');

    $parentID  = Category::where('catParentId', $category->idCategory)->get();

    try {

        if(empty($parentID[0]["catNo"]))
        {
            $category->delete();
            
            $request->session()->flash('message', ['result' => true,'title' => Lang::get('gest.deleteSucces.title'), 'msg' => Lang::get('gest.deleteSucces.msg', array('model' => $category->catNo.' - '.$category->catName))]);
        }
        else
        {
            $request->session()->flash('message', ['result' => false,'title' => Lang::get('gest.deleteCategoryParent.title'), 'msg' =>  Lang::get('gest.deleteCategoryParent.msg', array('category' => $category->catNo.' - '.$category->catName))]);
            return redirect('/gest/categories');
        }

    }
    catch (\Illuminate\Database\QueryException $e) {
        if ($e->errorInfo[1] == "1451")// SQL code 1451 = Constraint fail
            $request->session()->flash('message', ['result' => false,'title' => Lang::get('gest.deleteCategoryEchecContraintFail.title'), 'msg' =>  Lang::get('gest.deleteCategoryEchecContraintFail.msg', array('category' => $category->catNo.' - '.$category->catName))]);
        else
            $request->session()->flash('message', ['result' => false,'title' =>Lang::get('gest.deleteModelEchecUnknownError.title'), 'msg' =>  Lang::get('gest.deleteModelEchecUnknownError.msg', array('model' => $category->catNo.' - '.$category->catName))]);
    }
    return redirect()->back();
}

Category模型代码 #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Exceptions\InvalidArgumentExceptionClass;
use App\Traits\Uuids;
use Baum\NestedSet\Node;

class Category extends Model {
    use Node;
    use Uuids;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['catNo', 'catName'];

    /**
     * The attributes that aren't mass assignable.
     * @var array
     */
    protected $guarded = ['idCategory','catParentId','catLeft','catRight','catDepth'];

    /**
     * The attributes that should be hidden for arrays.
     * @var array
     */
    protected $hidden = ['idCategory','catParentId'];

    /**
     * The table associated with the model.
     * @var String
     */
    protected $table = 't_category';

    /**
     * The "type" of the primary key ID.
     *
     * @var string
     */
    protected $keyType = 'string';

    /**
     * The primary key for the model.
     * @var String
     */
    protected $primaryKey = "idCategory";

    /**
     * Indicates if the IDs are auto-incrementing.
     * @var boolean
     */
    public $incrementing = false;

    /**
     * Indicates if the timestamps crated_at and updated_at are saved.
     * @var boolean
     */
    public $timestamps = false;

    // 'parent_id' column name
    protected $parentColumnName = 'catParentId';

    // 'lft' column name
    protected $leftColumnName = 'catLeft';

    // 'rgt' column name
    protected $rightColumnName = 'catRight';

    // 'depth' column name
    protected $depthColumnName = 'catDepth';


    /**
     * Get the kits that belong to the category
     *
     * @return array Kit
     */
    public function kits() {
        return $this->hasMany(Kit::class,'fkCategory')
            ->orderBy('kitNo','asc');
    }

    /**
     * Get the Additional Items that belong to the category
     *
     * @return array AddItem
     */
    public function addItems() {
        return $this->hasMany(AddItem::class,'fkCategory')
            ->orderBy('adiName','asc');
    }

    /**
     * Create an Category
     *
     * @param array $attributes
     *
     * @return \App\Models\Category
     *
     * @throws \App\Exceptions\InvalidArgumentExceptionClass
     */
    public static function create(array $attributes = []) {
        // Check if not duplicate No
        if (static::where('catNo', $attributes['catNo'])->first()) {
            throw InvalidArgumentExceptionClass::modelWithNoAlreadyExist(Category::class,$attributes['catNo']);
        }

        return static::query()->create($attributes);
    }

    /**
     * Find a Category by its id
     *
     * @param string $idCategory
     *
     * @return \App\Models\Category|null
     */
    public static function findById(string $idCategory) {
        $category = static::where('idCategory', $idCategory)->first();

        if (! $category) {
            return null;
        }

        return $category;
    }

    /**
     * Find a Category by its No
     *
     * @param string $catNo
     *
     * @return \App\Models\Category|null
     */
    public static function findByNo(string $catNo) {
        $category = static::where('catNo', $catNo)->first();

        if (! $category) {
            return null;
        }

        return $category;
    }
}

原因与解决方案 #

为什么SQL不是按ID删除？ #

你的Category模型使用了Baum\NestedSet\Node trait，这个trait用于实现树形结构的嵌套集合，它会重写Laravel原生的delete()方法。默认调用delete()时，会删除当前节点及其所有子节点——这就是SQL用catLeft和catRight范围匹配的原因，嵌套集合中，一个节点的所有子节点的左右值都处于该节点的左右值区间内。

为什么触发约束错误？ #

错误码1451是外键约束失败，说明当前分类还关联着kits或addItems表中的记录，数据库不允许删除被关联的父记录。

具体解决步骤 #

1. 处理关联数据
   删除分类前必须清理关联记录，可选择删除关联数据或迁移到其他分类：

   // 删除该分类下的所有套件和附加项
   $category->kits()->delete();
   $category->addItems()->delete();
   

2. 修正子节点判断逻辑
   你当前判断子节点的写法冗余且不准确，Baum的Node trait提供了children()方法，可直接用exists()判断：

   // 替换原有的$parentID查询逻辑
   if (!$category->children()->exists()) {
       // 先清理关联数据
       $category->kits()->delete();
       $category->addItems()->delete();
       // 再删除分类
       $category->delete();
       // ... 成功提示逻辑
   } else {
       // ... 存在子节点的提示逻辑
   }
   

3. 仅删除单个节点（保留子节点）
   若无需删除子节点，可使用Baum提供的deleteWithoutChildren()方法，它会将子节点提升为当前节点的同级节点后再删除当前节点：

   // 先清理关联数据
   $category->kits()->delete();
   $category->addItems()->delete();
   // 删除当前节点，保留子节点
   $category->deleteWithoutChildren();
   

内容的提问来源于stack exchange，提问作者SamDev38